如何获取一个数据库的所有建表语句与创建索引的语句？

生成当前数据库下所有表的创建脚本 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‘) View Code

 

 

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

--生成表索引的创建删除语句

;
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 

View Code

 

 

查看表详情、数据字典

--查看表的详细信息


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 

View Code

 

如何获取一个数据库的所有建表语句与创建索引的语句？

标签：数据库名   one   生成   nullable   创建索引   temp   position   gif   添加