有一张系统视图 sys.object
在数据库内创建的每个用户定义的架构范围内的对象（包括本机编译的标量用户定义函数）都包含一行。
几乎所有对象信息都存在于sys.objects系统视图中，同时又在不同的系统视图中保留了相应的副本，对于函数、视图、 存储过程、触发器等相应的文本对象，把相应的对象的详细资料存于新的sys.sql_modules视图中。
一、查询数据库所有表和字段的基本信息
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