如何获取一个数据库的所有建表语句与创建索引的语句？
如何获取一个数据库的所有建表语句与创建索引的语句？
生成建表语句操作
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
