当前位置:Gxlcms > 数据库问题 > 如何获取一个数据库的所有建表语句与创建索引的语句?

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

时间:2021-07-01 10:21:17 帮助过:7人阅读

生成当前数据库下所有表的创建脚本 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索引包含列 ,
            NCREATE 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) + NINCLUDE ( + IX.index_columns_includes
                        + N)
                   ELSE N‘‘
              END AS N创建索引 ,
            NDROP 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   添加   

人气教程排行