查询表结构

简介:
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,a.name,'PRECISION'),  
    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'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 表名 = 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 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 
WHERE g.[value] IS NOT NULLORDER BY d.name,a.name------------------------------------------------------------------------当前数据库表大小及行数SELECT SCHEMA_NAME(tbl.schema_id) [Schema],tbl.name AS [TableName],
(CAST(ISNULL((select 8 * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) 
FROM sys.indexes as iJOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_idJOIN sys.allocation_units as a ON a.container_id = p.partition_idwhere i.object_id = tbl.object_id),0.0)*1.0/1024 AS DECIMAL(18,3))) AS [DataSpaceUsed(MB)],SI.[rows]FROM sys.tables AS tbl LEFT JOIN sys.sysindexes si ON tbl.object_id=si.id AND si.indid IN(0,1)ORDER BY [Schema],[DataSpaceUsed(MB)] DESC-------------------------------------------------------------------------------------------  查看表中的自增列是否为主键SELECT表名= D.NAME, 
列名= 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 '' ENDFROM SYSCOLUMNS ALEFT JOIN SYSTYPES B ON A.XUSERTYPE=B.XUSERTYPEINNER JOIN SYSOBJECTS D ON A.ID=D.ID AND D.XTYPE= 'U' AND D.NAME <> 'DTPROPERTIES 'where  COLUMNPROPERTY( A.ID,A.NAME, 'ISIDENTITY ')=1--------------------------------------------------------------------各表对象下的其他对象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],t1.name,t2.[type],t2.nameselect t1.id,t1.xtype,t1.name,t2.id,t2.xtype,t2.namefrom sys.sysobjects t1inner join sys.sysobjects t2 on t1.id=t2.parent_objorder by t1.xtype,t1.name,t2.xtype,t2.name-------------------------------------------------------------------------------唯一键约束SELECT tbl.name tab,i.name AS [Name]FROM sys.tables AS tblINNER JOIN sys.indexes AS i ON (i.index_id > 0 and 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'ORDER BY [Name] ASC--------------------------------------------------------------------------------------查看数据库约束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
,name 
,expiration_date 
from msdb.dbo.backupset 
where backup_start_date >= DATEADD(D,-7,GETDATE())-------------------------------------------------------------------------  作业启用情况和所有者select a.job_id,a.name,a.enabled,b.namefrom msdb.dbo.sysjobs ainner join master.sys.syslogins b on a.owner_sid=b.sid and a.owner_sid<>'0x01'order by 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_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,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 ORDER BY TableName,IndexName,isPrimaryKey,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,B.name,PrimaryKey 


SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME),'ISPRIMARYKEY')=1 --AND TABLE_NAME='TABLE_NAME'  ---------------------------------------------------------------------------------------------------------所有表索引对应的键列和包含列SELECT OBJECT_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 <> 0
    FOR 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 = 0
    FOR XML PATH('')),1,1,'') AS IncludeColsFROM sys.sysindexes t1 
WHERE t1.root is not nullAND EXISTS(SELECT * FROM sys.tables t4 WHERE t1.id=t4.object_id)ORDER BY tab,IndexCols--------------------------------------------------------------------------------------------------------------    查看表分区情况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   


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

本文转自 9pc9com 博客, 原文链接:    http://blog.51cto.com/215363/1930559    如需转载请自行联系原作者


相关文章
|
15天前
|
搜索推荐
舒尔特表练习记
舒尔特表练习记 1 练习的开始
40 0
|
15天前
|
SQL 前端开发 关系型数据库
MYSQL基础之【创建数据表,删除数据表】
MYSQL基础之【创建数据表,删除数据表】
42 0
|
9月前
|
Oracle 关系型数据库 MySQL
数据库——查询某个字段在哪些表中
数据库——查询某个字段在哪些表中
|
SQL 关系型数据库 MySQL
MySQL数据库 单表数据记录查询
MySQL数据库 单表数据记录查询
98 0
MySQL数据库 单表数据记录查询
|
关系型数据库 MySQL 数据库
MySQL数据库之单表记录查询
MySQL数据库之单表记录查询
173 0
MySQL数据库之单表记录查询
|
存储 SQL 数据库
数据表
数据表
123 0
|
SQL 关系型数据库 MySQL
MySQL查询数据表中数据记录(包括多表查询)
MySQL查询数据表中数据记录(包括多表查询)
355 0
|
SQL Web App开发 数据库
SQL查询某个数据库所有表名、表中字段名
一、查询某个数据库中所有数据表的名称 Select TABLE_NAME FROM DBTest02.INFORMATION_SCHEMA.TABLES Where TABLE_TYPE='BASE TABLE'   二、查询某个数据表中所有字段的信息 2.
1016 0
|
关系型数据库 MySQL 数据库
Mysql查询-表数据的查询
创建一个数据库 create database python_test charset=utf8; 使用一个数据库 use python_test; 显示使用的当前数据是那个? select database(); 创建一个数据表 create table students( id int unsigned not null auto_increment primary key, name varchar(30), age tinyint unsigned default 0, high decimal(5
168 0