SQLserver数据库中如何获取一个数据库的所有建表语句以及创建索引的语句
如何获取一个数据库的所有建表语句与创建索引的语句？
生成建表语句操作
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 + ', '<span style="color: #0000ff;">from</span> information_schema.columns <span style="color: #0000ff;">where</span> table_name <span style="color: #808080;">=</span><span style="color: #000000;"> so.name </span><span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span><span style="color: #000000;"> ordinal_position </span><span style="color: #0000ff;">FOR</span> XML PATH(<span style="color: #ff0000;">''</span><span style="color: #000000;">)) 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
