请问如何查询一个数据库的所有建表语句？

生成建表语句操作

　　SSMS中 右击数据库-》生成脚本-》选择仅限架构

利用T-SQL生成建表语句

复制代码

复制代码 --生成当前数据库下所有表的创建脚本 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')

复制代码 复制代码

生成表索引创建、删除语句

复制代码

复制代码 --生成表索引的创建删除语句

; 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 表名 = CASE WHEN C.column_id = 1 THEN O.name

ELSE N''

END ,

表说明 = ISNULL(CASE WHEN C.column_id = 1 THEN PTB.[value]

END, N'') ,

字段序号 = C.column_id ,

字段名 = C.name ,

主键 = ISNULL(IDX.PrimaryKey, N'') ,

标识 = CASE WHEN C.is_identity = 1 THEN N'√'

ELSE N''

END ,

计算列 = CASE WHEN C.is_computed = 1 THEN N'√'

ELSE N''

END ,

类型 = T.name ,

长度 = C.max_length ,

精度 = C.precision ,

小数位数 = C.scale ,

允许空 = CASE WHEN C.is_nullable = 1 THEN N'√'

ELSE N''

END ,

默认值 = ISNULL(D.definition, N'') ,

字段说明 = ISNULL(PFD.[value], N'') ,

索引名 = ISNULL(IDX.IndexName, N'') ,

索引排序 = ISNULL(IDX.Sort, N'') ,

创建时间 = O.Create_Date ,

修改时间 = O.Modify_date

FROM sys.columns C

INNER JOIN sys.objects O ON C.[object_id] = O.[object_id]

AND O.type = 'U'

AND O.is_ms_shipped = 0

INNER JOIN sys.types T ON C.user_type_id = T.user_type_id

LEFT JOIN sys.default_constraints D ON C.[object_id] = D.parent_object_id

AND C.column_id = D.parent_column_id

AND C.default_object_id = D.[object_id]

LEFT JOIN sys.extended_properties PFD ON PFD.class = 1

AND C.[object_id] = PFD.major_id

AND C.column_id = PFD.minor_id

-- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)

LEFT JOIN sys.extended_properties PTB ON PTB.class = 1

AND PTB.minor_id = 0

AND C.[object_id] = PTB.major_id

-- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)

LEFT JOIN -- 索引及主键信息

( SELECT IDXC.[object_id] ,

IDXC.column_id ,

Sort = CASE INDEXKEY_PROPERTY(IDXC.[object_id],

IDXC.index_id,

IDXC.index_column_id,

'IsDescending')

WHEN 1 THEN 'DESC'

WHEN 0 THEN 'ASC'

ELSE ''

END ,

PrimaryKey = CASE WHEN IDX.is_primary_key = 1 THEN N'√'

ELSE N''

END ,

IndexName = IDX.Name

FROM sys.indexes IDX

INNER JOIN sys.index_columns IDXC ON IDX.[object_id] = IDXC.[object_id]

AND IDX.index_id = IDXC.index_id

LEFT JOIN sys.key_constraints KC ON IDX.[object_id] = KC.[parent_object_id]

AND IDX.index_id = KC.unique_index_id

INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息

( SELECT [object_id] ,

Column_id ,

index_id = MIN(index_id)

FROM sys.index_columns

GROUP BY [object_id] ,

Column_id

) IDXCUQ ON IDXC.[object_id] = IDXCUQ.[object_id]

AND IDXC.Column_id = IDXCUQ.Column_id

AND IDXC.index_id = IDXCUQ.index_id

) IDX ON C.[object_id] = IDX.[object_id]

AND C.column_id = IDX.column_id

-- WHERE O.name like '%name%'

-- 如果只查询指定表,加上此条件

ORDER BY O.name ,

C.column_id