丫丫小强的个人博客

查询所有表、索引、视图信息的SQL语句-MsSql

有一张系统视图   sys.object

在数据库内创建的每个用户定义的架构范围内的对象(包括本机编译的标量用户定义函数)都包含一行。

在开发文档里有具体描述:https://docs.microsoft.com/zh-cn/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=sql-server-ver15 

几乎所有对象信息都存在于sys.objects系统视图中,同时又在不同的系统视图中保留了相应的副本,对于函数、视图、 存储过程、触发器等相应的文本对象,把相应的对象的详细资料存于新的sys.sql_modules视图中。 

表视图:https://docs.microsoft.com/zh-cn/sql/relational-databases/system-catalog-views/sys-tables-transact-sql?view=sql-server-ver15

一、查询数据库所有表和字段的基本信息   

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 = 'Test'--如果只查询指定表,加上此where条件,tablename是要查询的表名;去除where条件查询所有的表信息
order by
           a.id,a.colorder

 

查询数据库所有建表语句: 

select 'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from sysobjects so
cross apply
(select
           ' ['+column_name+'] ' +
           data_type + case data_type
           when 'sql_variant' then ''
           when 'text' then ''
           when 'decimal' then '(' + cast(numeric_precision_radix as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
           else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
           case when exists (
select id from syscolumns
where  object_name(id)=so.name
           and name=column_name
           and columnproperty(id,name,'IsIdentity') = 1
) then
           'IDENTITY(' +
           cast(ident_seed(so.name) as varchar) + ',' +
           cast(ident_incr(so.name) as varchar) + ')'
           else ''
           end + ' ' +
           (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' +
           case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', '
from information_schema.columns where table_name = so.name
ORDER BY ordinal_position
FOR XML PATH('')) o (list)
LEFT JOIN
information_schema.table_constraints tc
on tc.Table_name = so.Name
AND tc.Constraint_Type = 'PRIMARY KEY'
cross apply
(select '[' + Column_Name + '], '
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH('')) j (list)
where xtype = 'U'
AND name NOT IN ('dtproperties');


查询整个数据库包括系统表所有的字段信息

SELECT * FROM SYS.COLUMNS

 

二、查询数据库所有表的索引信息

注:还可以生成创建和删除语句 

WITH TB
           AS ( SELECT    TB.object_id ,
                                  Schema_name = Sch.name ,
                                  table_name = TB.name
                   FROM      sys.tables TB
                                   INNER JOIN sys.schemas Sch ON TB.schema_id = Sch.schema_id
                   WHERE    TB.is_ms_shipped = 0
                    ),
             IXC
                AS ( SELECT IXC.object_id ,
                                    IXC.index_id ,
                                    IXC.index_column_id ,
                                    IXC.is_descending_key ,
                                    IXC.is_included_column ,
                                    column_name = C.name
                        FROM  SYS.index_columns IXC
                                    INNER JOIN SYS.columns C ON IXC.object_id = C.object_id
                                                                                        AND IXC.column_id = C.column_id
                 ),
IX
    AS ( SELECT       IX.object_id ,
                              index_name = IX.name ,
                              index_type_desc = IX.type_desc ,
                              IX.is_unique ,
                              IX.is_primary_key ,
                              IX.is_unique_constraint ,
                              IX.is_disabled ,
                              index_columns_TEMP = STUFF(IXC_COL.index_columns, 1, 1,
                                                                                 N'') ,
                              index_columns = CASE WHEN IXC_COL_INCLUDE.index_columns_includes IS NOT NULL
                                                                   THEN STUFF(LEFT(IXC_COL.index_columns,
                                                                                                DATALENGTH(IXC_COL.index_columns)
                                                                                                 -
DATALENGTH(IXC_COL_INCLUDE.index_columns_includes)),
                                                     1, 1, N'')
                                          ELSE STUFF(IXC_COL.index_columns,
                                                     1, 1, N'')
                                       END ,
                          index_columns_includes = STUFF(IXC_COL_INCLUDE.index_columns_includes,
                                                     1, 1, N'')
            FROM   sys.indexes IX
                    CROSS APPLY ( SELECT index_columns = ( SELECT
                                                          N','
                                                          + QUOTENAME(column_name)
                                                          FROM
                                                          IXC
                                                          WHERE
                                                         object_id = IX.object_id
                                                         AND index_id = IX.index_id
                                                         ORDER BY index_column_id
                             FOR   XML PATH('') ,
                                       ROOT('r') ,
                                       TYPE
).value('/r[1]', 'nvarchar(max)')
                               ) IXC_COL
                      OUTER APPLY ( SELECT index_columns_includes = ( SELECT
                                                         N','
                                                         + QUOTENAME(column_name)
                                                         FROM
                                                         IXC
                                                         WHERE
                                                         object_id = IX.object_id
                                                         AND index_id = IX.index_id
                                                         AND is_included_column = 1
                                                         ORDER BY index_column_id
                                   FOR     XML PATH('') ,
                                              ROOT('r') ,
                                              TYPE
).value('/r[1]', 'nvarchar(max)')
                     ) IXC_COL_INCLUDE
           WHERE index_id > 0
          )
SELECT DB_NAME() AS N'数据库名' ,
       TB.Schema_name AS N'架构' ,
       TB.table_name AS N'表名' ,
       IX.index_name AS N'索引名' ,
       IX.index_type_desc AS N'索引类型' ,
       IX.is_unique AS N'是否唯一索引' ,
       IX.is_primary_key AS N'是否主键' ,
       IX.is_unique_constraint AS N'是否唯一约束' ,
       IX.is_disabled AS N'是否禁用索引' ,
       IX.index_columns AS N'索引列' ,
       IX.index_columns_includes AS N'索引包含列' ,
       N'CREATE INDEX ' + N'[' + IX.index_name + N']' + N' ON ' + N'['
       + QUOTENAME(DB_NAME()) + N'.' + QUOTENAME(TB.SCHEMA_NAME) + N'.'
       + QUOTENAME(TB.table_name) + N'(' + IX.index_columns + N')'
       + CASE WHEN IX.index_columns_includes IS NOT NULL
              THEN CHAR(13) + N'INCLUDE (' + IX.index_columns_includes
              + N')'
       ELSE N''
   END AS N'创建索引' ,
       N'DROP INDEX ' + QUOTENAME(IX.index_name) + N' ON '
       + QUOTENAME(DB_NAME()) + N'.' + QUOTENAME(TB.SCHEMA_NAME) + N'.'
       + QUOTENAME(TB.table_name) AS N'删除索引'
FROM TB
       INNER JOIN IX ON TB.object_id = IX.object_id
ORDER BY Schema_name ,
       table_name ,
       IX.index_name;

 结果图:

 


三、查询建立的所有视图信息 

select * from sys.objects where xtype='V'

在SQL server的可视化工具Microsoft SQL Server Management Studio 中右键点击视图,设计中可查看创建语句


 

————————————————

版权声明:本文为CSDN博主「黑烟」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。

原文链接:https://blog.csdn.net/weixin_43912785/article/details/113320068