SQLServer 表结构相关查询（快速了解数据库）

                --
                    表结构查询
                SELECT
                   
    表名       
                =
                case
                when a.colorder=1then d.name else‘‘end,  
    表说明     =casewhen a.colorder=1thenisnull(f.value,‘‘) else‘‘end,  
    字段序号   = a.colorder,  
    字段名     = a.name,  
    标识       =casewhenCOLUMNPROPERTY( a.id,a.name,‘IsIdentity‘)=1then‘√‘else‘‘end,  
    主键       =casewhenexists(SELECT1FROM sysobjects where xtype=‘PK‘and parent_obj=a.id and name in (  
                     SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then‘√‘else‘‘end,  
    类型       = b.name,  
    占用字节数 = a.length,  
    长度       =COLUMNPROPERTY(a.id,a.name,‘PRECISION‘),  
    小数位数   =isnull(COLUMNPROPERTY(a.id,a.name,‘Scale‘),0),  
    允许空     =casewhen a.isnullable=1then‘√‘else‘‘end,  
    默认值     =isnull(e.text,‘‘),  
    字段说明   =isnull(g.[value],‘‘)  
FROM syscolumns a leftjoin systypes b   
on a.xusertype=b.xusertype  
innerjoin sysobjects d   
on a.id=d.id  and d.xtype=‘U‘and  d.name<>‘dtproperties‘leftjoin syscomments e   
on a.cdefault=e.id  
leftjoin sys.extended_properties   g   
on a.id=g.major_id and a.colid=g.minor_id    
leftjoin sys.extended_properties f  
on d.id=f.major_id and f.minor_id=0where d.name=‘tableName‘--如果只查询指定表,加上此条件  orderby a.id,a.colorder 

-------------------------------------------------------------------------------------------------
--查看数据库中所有外键select oMain.name  AS[主表名称]
,oSub.name  AS[子表名称]
,fk.name AS[外键名称]
,MainCol.name AS[主表列名]
,SubCol.name AS[子表列名]from sys.foreign_keys fk  
JOIN sys.all_objects oSub  ON (fk.parent_object_id = oSub.object_id)
JOIN sys.all_objects oMain ON (fk.referenced_object_id = oMain.object_id)
JOIN sys.foreign_key_columns fkCols ON (fk.object_id= fkCols.constraint_object_id)
JOIN sys.columns SubCol ON (oSub.object_id= SubCol.object_idAND fkCols.parent_column_id = SubCol.column_id)
JOIN sys.columns MainCol ON (oMain.object_id= MainCol.object_idAND fkCols.referenced_column_id = MainCol.column_id)
    
--------------------------------------------------------------------------------------(导出扩展属性脚本)SELECT 表名 = d.name,字段名 = a.name, 字段说明 =isnull(g.[value],‘‘) 
,‘EXEC sys.sp_addextendedproperty @name=N‘‘MS_Description‘‘, @value=N‘‘‘+CONVERT(VARCHAR(MAX),g.[value])
+‘‘‘,@level0type=N‘‘SCHEMA‘‘,@level0name=N‘‘dbo‘‘,@level1type=N‘‘TABLE‘‘,@level1name=N‘‘‘+CONVERT(VARCHAR(MAX),d.name)
+‘‘‘,@level2type=N‘‘COLUMN‘‘,@level2name=N‘‘‘+CONVERT(VARCHAR(MAX),a.name)+‘‘‘‘FROM syscolumns a leftjoin systypes b on a.xusertype=b.xusertype    
innerjoin sysobjects d on a.id=d.id  and d.xtype=‘U‘and  d.name<>‘dtproperties‘leftjoin syscomments e on a.cdefault=e.id    
leftjoin sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id 
WHERE g.[value]ISNOTNULLORDERBY d.name,a.name



------------------------------------------------------------------------当前数据库表大小及行数SELECT SCHEMA_NAME(tbl.schema_id) [Schema],tbl.name AS[TableName],
(CAST(ISNULL((select8*SUM(CASEWHEN a.type <>1THEN a.used_pages WHEN p.index_id <2THEN a.data_pages ELSE0END) 
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id= i.object_idand p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
where i.object_id= tbl.object_id),0.0)*1.0/1024ASDECIMAL(18,3))) AS[DataSpaceUsed(MB)],SI.[rows]FROM sys.tables AS tbl LEFTJOIN sys.sysindexes si ON tbl.object_id=si.id AND si.indid IN(0,1)
ORDERBY[Schema],[DataSpaceUsed(MB)]DESC-------------------------------------------------------------------------------------------  查看表中的自增列是否为主键SELECT
表名= D.NAME, 
列名= A.NAME,
是否自增=CASEWHENCOLUMNPROPERTY( A.ID,A.NAME, ‘ISIDENTITY ‘)=1THEN‘√‘ELSE‘‘END,
主键=CASEWHENEXISTS(SELECT1FROM SYSOBJECTS WHERE XTYPE=‘PK ‘AND PARENT_OBJ=A.ID AND NAME IN (
    SELECT NAME FROM SYSINDEXES WHERE INDID IN(
    SELECT INDID FROM SYSINDEXKEYS WHERE ID = A.ID AND COLID=A.COLID))) THEN‘√‘ELSE‘‘ENDFROM SYSCOLUMNS A
LEFTJOIN SYSTYPES B ON A.XUSERTYPE=B.XUSERTYPE
INNERJOIN SYSOBJECTS D ON A.ID=D.ID AND D.XTYPE=‘U‘AND D.NAME <>‘DTPROPERTIES ‘whereCOLUMNPROPERTY( A.ID,A.NAME, ‘ISIDENTITY ‘)=1--------------------------------------------------------------------各表对象下的其他对象select t1.[object_id],t1.[type],t1.name,t2.[object_id],t2.[type],t2.name
from sys.objects t1
innerjoin sys.objects t2 on t1.[object_id]=t2.parent_object_id
orderby t1.[type],t1.name,t2.[type],t2.name

select t1.id,t1.xtype,t1.name,t2.id,t2.xtype,t2.name
from sys.sysobjects t1
innerjoin sys.sysobjects t2 on t1.id=t2.parent_obj
orderby t1.xtype,t1.name,t2.xtype,t2.name


-----------------------------------------------------------------------------
--唯一键约束SELECT tbl.name tab,i.name AS[Name]FROM sys.tables AS tbl
INNERJOIN sys.indexes AS i ON (i.index_id >0and i.is_hypothetical =0) AND (i.object_id=tbl.object_id)
WHERE (i.is_primary_key +2*i.is_unique_constraint=2) and SCHEMA_NAME(tbl.schema_id)=‘dbo‘ORDERBY[Name]ASC------------------------------------------------------------------------------------
--查看数据库约束SELECTOBJECT_NAME(parent_object_id) as TableName,name,definition
FROM sys.default_constraints ORDERBY TableName,name

-----------------------------------------------------------------------------------------表各列约束selectOBJECT_NAME(t2.object_id) as TabName,t2.name as ColumnName,t1.name as[Constraint]from sys.default_constraints t1
innerjoin sys.columns t2 
on t1.parent_object_id=t2.object_idand t1.parent_column_id=t2.column_id
orderby TabName,ColumnName,[Constraint]------------------------------------------------------------------------------------    当前数据库文件增长设置情况SELECT Name, FileName
, CAST((Size *8/1024) ASvarchar(10)) +‘MB‘AS FileSize
, MaxSize =CASE MaxSize WHEN-1THEN‘Unlimited‘ELSECAST((Maxsize /128) ASvarchar(10)) +‘MB‘ENDFROM sys.sysfiles;

--    所有数据库文件增长设置情况selectDB_NAME(database_id) as dbName,file_id,(size*8/1024)  as[size(mb)]
,casewhen is_percent_growth =1then‘10%‘elseCONVERT(varchar(10),growth*8/1024)+‘M‘endas growth
,type_desc,physical_name 
from sys.master_files 
where state =0--and database_id=DB_id()-------------------------------------------------------------------------------------------------------------------数据库的一些关键属性SELECT db.[name]AS[Database Name], db.recovery_model_desc AS[Recovery Model],  
db.log_reuse_wait_desc AS[Log Reuse Wait Description],  
ls.cntr_value AS[Log Size (KB)], lu.cntr_value AS[Log Used (KB)],  
CAST(CAST(lu.cntr_value ASFLOAT) /CAST(ls.cntr_value ASFLOAT)ASDECIMAL(18,2)) *AS[Log Used %], db.[compatibility_level]AS[DB Compatibility Level],  
db.page_verify_option_desc AS[Page Verify Option], db.is_auto_create_stats_on,  
db.is_auto_update_stats_on, db.is_auto_update_stats_async_on,  
db.is_parameterization_forced,  
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,  
is_auto_shrink_on, is_auto_close_on  
FROM sys.databases AS db WITH (NOLOCK)  
INNERJOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)  
ON db.name = lu.instance_name  
INNERJOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK)  
ON db.name = ls.instance_name  
WHERE lu.counter_name LIKE N‘Log File(s) Used Size (KB)%‘AND ls.counter_name LIKE N‘Log File(s) Size (KB)%‘AND ls.cntr_value >0OPTION (RECOMPILE);  


------------------------------------------------------------------------------------------最近一周内数据库备份情况SELECTuser_nameAS[User]
,server_name AS[Server]
,database_name AS[Database]
,recovery_model AS RecoveryModel 
,case type when‘D‘then‘数据库‘when‘I‘then‘差异数据库‘when‘L‘then‘日志‘when‘F‘then‘文件或文件组‘when‘G‘then‘差异文件‘when‘P‘then‘部分‘when‘Q‘then‘差异部分‘else type endas[backupType]
,convert(numeric(10,2),backup_size/1024/1024) as[Size(M)]
,backup_start_date AS backupStartTime
,backup_finish_date as backupFinishTime
,name 
,expiration_date 
from msdb.dbo.backupset 
where backup_start_date >=DATEADD(D,-7,GETDATE())


-------------------------------------------------------------------------  作业启用情况和所有者select a.job_id,a.name,a.enabled,b.name
from msdb.dbo.sysjobs a
innerjoin master.sys.syslogins b on a.owner_sid=b.sid and a.owner_sid<>‘0x01‘orderby a.name

--  更改作业所有者EXEC msdb.dbo.sp_update_job @job_id=N‘job_id‘, @owner_login_name=N‘sa‘----------------------------------------------------------------------------------------------
--    索引 主键/类型/列 情况
;with tb as(
SELECT tbl.name AS TableName,i.name AS IndexName,clmns.name AS ColumName,i.is_primary_key AS isPrimaryKey,i.type_desc
FROM sys.tables AS tbl  
INNERJOIN sys.indexes AS i ON (i.index_id >0and i.is_hypothetical =0) AND (i.object_id=tbl.object_id)  
INNERJOIN sys.index_columns AS ic ON (ic.column_id >0AND (ic.key_ordinal >0or ic.partition_ordinal =0or ic.is_included_column !=0))   
    AND (ic.index_id=CAST(i.index_id ASint) AND ic.object_id=i.object_id)  
INNERJOIN sys.columns AS clmns ON clmns.object_id= ic.object_idand clmns.column_id = ic.column_id  
WHERE SCHEMA_NAME(tbl.schema_id) = N‘dbo‘
)
SELECTDISTINCT TableName,IndexName,isPrimaryKey,type_desc
,STUFF((SELECT‘,‘+ColumName FROM tb B WHERE A.TableName=B.TableName AND A.IndexName=B.IndexName FOR XML PATH(‘‘)),1,1,‘‘) AS ColumName    
FROM tb A ORDERBY TableName,IndexName,isPrimaryKey,type_desc

------------------------------------------------------------------------------------------------------------------------------------------------------------表主键对应的列 SELECTOBJECT_NAME(C.id) AS TAB,B.name,A.name AS PrimaryKey ,E.type_desc,fill_factor  
FROM SYSCOLUMNS A,SYSOBJECTS B,SYSINDEXES C,SYSINDEXKEYS D , SYS.INDEXES E  
WHERE B.xtype =‘PK‘AND B.parent_obj = A.id     
AND C.id = A.id     
AND B.name = C.name     
AND D.id = A.id     
AND D.indid = C.indid    
AND A.colid = D.colid  
AND B.name=E.name  
ORDERBY TAB,B.name,PrimaryKey 


SELECT*FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHEREOBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME),‘ISPRIMARYKEY‘)=1--AND TABLE_NAME=‘TABLE_NAME‘  ---------------------------------------------------------------------------------------------------------所有表索引对应的键列和包含列SELECTOBJECT_NAME(t1.id) as tab,t1.name
,STUFF((SELECT‘,‘+t3.name FROM sys.sysindexkeys t2,sys.syscolumns t3 
    WHERE t1.indid=t2.indid and t1.id=t2.id and t2.id=t3.id and t2.colid=t3.colid and t2.keyno <>0FOR XML PATH(‘‘)),1,1,‘‘) AS IndexCols
,STUFF((SELECT‘,‘+t3.name FROM sys.sysindexkeys t2,sys.syscolumns t3 
    WHERE t1.indid=t2.indid and t1.id=t2.id and t2.id=t3.id and t2.colid=t3.colid and t2.keyno =0FOR XML PATH(‘‘)),1,1,‘‘) AS IncludeCols
FROM sys.sysindexes t1 
WHERE t1.root isnotnullANDEXISTS(SELECT*FROM sys.tables t4 WHERE t1.id=t4.object_id)
ORDERBY tab,IndexCols

------------------------------------------------------------------------------------------------------------
--    查看表分区情况selectOBJECT_NAME(object_id) as tab,COUNT(partition_number) as part 
from sys.partitions 
where index_id in(0,1)
andOBJECT_NAME(object_id) notlike‘conflict%‘andOBJECT_NAME(object_id) notlike‘sys%‘groupbyobject_idorderby tab

-------------------------------------------------------------------------------------------
--    查看表备注信息selectdistinct 
 表名 =casewhen a.colorder=1then d.name else‘‘end
,表说明 =casewhen a.colorder=1thenisnull(f.value,‘‘) else‘‘endfrom syscolumns a 
innerjoin sysobjects d on a.id=d.id  and d.xtype=‘U‘and  d.name<>‘dtproperties‘innerjoin sys.extended_properties f  on d.id=f.major_id
where f.minor_id=0--and CHARINDEX(‘‘,convert(varchar(max),f.value))<>0 ---------------------------------------------------------------------------------------------    查看表中各列的属性及创建扩展属性脚本（默认架构dbo）select o.name,c.name,p.name,p.value
,N‘EXEC sys.sp_addextendedproperty @name=N‘‘‘+p.name+ N‘‘‘, @value=N‘‘‘+convert(nvarchar(4000),p.value)
+N‘‘‘ , @level0type=N‘‘SCHEMA‘‘,@level0name=N‘‘dbo‘‘, @level1type=N‘‘TABLE‘‘,@level1name=N‘‘‘+o.name+ N‘‘‘, @level2type=N‘‘COLUMN‘‘,@level2name=N‘‘‘+c.name+ N‘‘‘‘as script_addextendedproperty
from sys.sysobjects o 
innerjoin sys.syscolumns c on o.id = c.id
innerjoin sys.extended_properties p on c.id=p.major_id and c.colid=p.minor_id   
where o.xtype = N‘U‘--and o.name = ‘tableName‘--------------------------------------------------------------------------------------------- 查看对象定义脚本  
--exec sp_helptext ‘object_name‘  SELECT*from INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE =‘FUNCTION‘AND ROUTINE_NAME=‘‘SELECT*from INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE =‘PROCEDURE‘AND ROUTINE_NAME=‘‘SELECT*from sys.sql_modules M WHEREEXISTS(SELECT*from sys.triggers T WHERE M.object_id=T.object_id)

SELECT o.name,o.type,o.create_date,o.modify_date,sm.definition  
FROM sys.sql_modules sm innerjoin sys.objects o on sm.object_id=o.object_idORDERBY o.type,o.name  
  
SELECT*from sys.sql_modules   
SELECT*from sys.all_sql_modules   
SELECT*from sys.system_sql_modules   


--------------------------------------------------------------------------------------------------------------------