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

概述-- 表结构查询SELECT 表名 = case when a.colorder=1 then d.name else '' end, 表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end, 字段序号 = a.colorder, 字段名 =

--	表结构查询SELECT       表名       = case when a.colorder=1 then d.name else '' end,表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,字段序号   = a.colorder,字段名     = a.name,标识       = case when ColUMNPROPERTY( a.ID,a.name,'IsIDentity')=1 then '√'else '' end,主键       = case when exists(SELECT 1 FROM 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,'PRECISION'),小数位数   = isnull(ColUMNPROPERTY(a.ID,'Scale'),0),允许空     = case when a.isnullable=1 then '√'else '' end,默认值     = isnull(e.text,''),字段说明   = isnull(g.[value],'')  FROM syscolumns a left join systypes b   on a.xusertype=b.xusertype  inner join sysobjects d   on a.ID=d.ID  and d.xtype='U' and  d.name<>'dtpropertIEs'  left join syscomments e   on a.cdefault=e.ID  left join sys.extended_propertIEs   g   on a.ID=g.major_ID and a.colID=g.minor_ID    left join sys.extended_propertIEs f  on d.ID=f.major_ID and f.minor_ID=0  where d.name='tablename'    --如果只查询指定表,加上此条件  order by 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_ID      AND fkCols.parent_column_ID = SubCol.column_ID)JOIN sys.columns MainCol ON (oMain.object_ID = MainCol.object_ID      AND fkCols.referenced_column_ID = MainCol.column_ID)

--各表对象下的其他对象select t1.[object_ID],t1.[type],t1.name,t2.[object_ID],t2.[type],t2.namefrom sys.objects t1inner join sys.objects t2 on t1.[object_ID]=t2.parent_object_IDorder by t1.[type],t2.nameselect t1.ID,t1.xtype,t2.ID,t2.xtype,t2.namefrom sys.sysobjects t1inner join sys.sysobjects t2 on t1.ID=t2.parent_objorder by t1.xtype,t2.name

--查看数据库约束SELECT OBJECT_name(parent_object_ID) as tablename,name,deFinitionFROM sys.default_constraints ORDER BY tablename,name

--表各列约束select OBJECT_name(t2.object_ID) as Tabname,t2.name as Columnname,t1.name as [Constraint]from sys.default_constraints t1inner join sys.columns t2 on t1.parent_object_ID=t2.object_ID and t1.parent_column_ID=t2.column_IDorder by Tabname,Columnname,[Constraint]

--	当前数据库文件增长设置情况SELECT name,filename,CAST((Size * 8 / 1024) AS varchar(10)) + 'MB' AS fileSize,MaxSize = CASE MaxSize WHEN -1 THEN 'Unlimited' ELSE CAST((Maxsize / 128) AS varchar(10)) + 'MB' END FROM sys.sysfiles;--	所有数据库文件增长设置情况select DB_name(database_ID) as dbname,file_ID,(size*8/1024)  as [size(mb)],case when is_percent_growth = 1 then '10%' else CONVERT(varchar(10),growth*8/1024)+'M' end as 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 AS float) / CAST(ls.cntr_value AS float)AS DECIMAL(18,2)) *  100 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)  INNER JOIN sys.dm_os_performance_counters AS lu WITH (NolOCK)  ON db.name = lu.instance_name  INNER JOIN 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 > 0 OPTION (RECOMPILE);  

--最近一周内数据库备份情况SELECT user_name AS [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 end as [backupType],convert(numeric(10,2),backup_size/1024/1024) as [Size(M)],backup_start_date AS backupStartTime,backup_finish_date as backupFinishTime,expiration_date from msdb.dbo.backupset where backup_start_date >= DATEADD(D,-7,GETDATE())

--	索引 主键/类型/列 情况;with tb as(SELECT tbl.name AS tablename,i.name AS Indexname,clmns.name AS Columname,i.is_primary_key AS isPrimaryKey,i.type_descFROM sys.tables AS tbl  INNER JOIN sys.indexes AS i ON (i.index_ID > 0 and i.is_hypothetical = 0) AND (i.object_ID=tbl.object_ID)  INNER JOIN sys.index_columns AS ic ON (ic.column_ID > 0       AND (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0))       AND (ic.index_ID=CAST(i.index_ID AS int) AND ic.object_ID=i.object_ID)  INNER JOIN sys.columns AS clmns ON clmns.object_ID = ic.object_ID and clmns.column_ID = ic.column_ID  WHERE SCHEMA_name(tbl.schema_ID) = N'dbo')SELECT disTINCT tablename,Indexname,isPrimaryKey,STUFF((SELECT ','+Columname FROM tb B WHERE A.tablename=B.tablename AND A.Indexname=B.Indexname FOR XML PATH('')),1,'') AS Columname    FROM tb A ORDER BY tablename,type_desc

--表主键对应的列 SELECT OBJECT_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  ORDER BY TAB,PrimaryKey SELECT * FROM informatION_SCHEMA.KEY_ColUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_name),'ISPRIMARYKEY')=1 --AND table_name='table_name'  

--	查看表分区情况select OBJECT_name(object_ID) as tab,COUNT(partition_number) as part from sys.partitions where index_ID in(0,1)and OBJECT_name(object_ID) not like 'conflict%'and OBJECT_name(object_ID) not like 'sys%'group by object_ID order by tab

--	查看表备注信息select distinct  表名 = case when a.colorder=1 then d.name else '' end,表说明 = case when a.colorder=1 then isnull(f.value,'') else '' endfrom syscolumns a inner join sysobjects d on a.ID=d.ID  and d.xtype='U' and  d.name<>'dtpropertIEs' inner join sys.extended_propertIEs f  on d.ID=f.major_IDwhere 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_addextendedpropertyfrom sys.sysobjects o inner join sys.syscolumns c on o.ID = c.IDinner join 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 WHERE EXISTS(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 inner join sys.objects o on sm.object_ID=o.object_ID  ORDER BY o.type,o.name    SELECT * from sys.sql_modules   SELECT * from sys.all_sql_modules   SELECT * from sys.system_sql_modules   

总结

以上是内存溢出为你收集整理的SQLServer 表结构相关查询（快速了解数据库）全部内容，希望文章能够帮你解决SQLServer 表结构相关查询（快速了解数据库）所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。