SqlServer根据表名生成建表语句的存储过程

CREATE PROCEDURE [dbo].[usp_CreateTable_DDL]

(

        @sTable_Name        SYSNAME,

        @Create_Table_Ind    BIT = 1,

        @PK_Ind                BIT = 1,

        @FK_Ind                BIT = 1,

        @Check_Ind            BIT = 1,

        @Default_Ind        BIT = 1

 )

AS

BEGIN

    SET NOCOUNT ON

    DECLARE @Schema_Name        SYSNAME,

            @UniqueConstraints    BIT = 1,

            @sStr                VARCHAR(MAX)

    SELECT    @Schema_Name = SCHEMA_NAME(schema_id)

    FROM    sys.objects

    WHERE    name = @sTable_Name

    

    IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE ID = OBJECT_ID('tempdb..#PKObjectID'))    DROP TABLE #PKObjectID

    IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE ID = OBJECT_ID('tempdb..#Uniques'))        DROP TABLE #Uniques

    IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE ID = OBJECT_ID('tempdb..#Constraints'))    DROP TABLE #Constraints

    IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE ID = OBJECT_ID('tempdb..#ShowFields'))    DROP TABLE #ShowFields

    CREATE TABLE #Constraints (ID INT IDENTITY, Constraint_Type VARCHAR(100), SQL VARCHAR(8000), Constraint_Name SYSNAME DEFAULT '')

    -- Create table

    IF @Create_Table_Ind = 1

    BEGIN

        SELECT  FieldID               = IDENTITY(INT,1,1),

                DatabaseName            = DB_NAME(),

                TableOwner                = TABLE_SCHEMA,

                TableName                = TABLE_NAME,

                FieldName                = COLUMN_NAME,

                ColumnPosition            = CAST(ORDINAL_POSITION AS INT),

                ColumnDefaultValue        = COLUMN_DEFAULT,

                ColumnDefaultName        = dobj.name,

                IsNullable                = CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,

                DataType                = DATA_TYPE,

                MaxLength                = CAST(CHARACTER_MAXIMUM_LENGTH AS INT),

                NumericPrecision        = CAST(NUMERIC_PRECISION AS INT),

                NumericScale            = CAST(NUMERIC_SCALE AS INT),

                DomainName                = DOMAIN_NAME,

                FieldListingName        = COLUMN_NAME + ',',

                FieldDefinition            = '',

                IdentityColumn            = CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END,

                IdentitySeed            = CAST(ISNULL(ic.seed_value,0) AS INT),

                IdentityIncrement        = CAST(ISNULL(ic.increment_value,0) AS INT),

                IsCharColumn            = CASE WHEN DATA_TYPE NOT IN ('TEXT') AND st.collation_name IS NOT NULL THEN 1 ELSE 0 END

        INTO    #ShowFields

        FROM    INFORMATION_SCHEMA.COLUMNS            c

                JOIN sys.columns                    sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name

                LEFT JOIN sys.identity_columns        ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name

                JOIN sys.types                        st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name

                LEFT OUTER JOIN sys.objects            dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'

        WHERE    c.TABLE_NAME = @sTable_Name

        ORDER    BY c.TABLE_NAME, c.ORDINAL_POSITION

        SELECT    @sStr = 'IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id('+'N'''+ '[dbo]' + '.' + QUOTENAME(@sTable_Name) + ''')'+ ' AND OBJECTPROPERTY(id, N'''+'IsUserTable'+''')'+'= 1)' + char(13) + char(10)

                        + 'DROP TABLE ' + '[dbo]' + '.' + QUOTENAME(@sTable_Name) + char(13) + char(10)

                        + 'CREATE TABLE ' + '[dbo]' + '.' + QUOTENAME(@sTable_Name) + '('

        SELECT    @sStr = @sStr + 

                CHAR(10) + CHAR(9) + QUOTENAME(FieldName) + ' ' +

                    CASE

                        WHEN DomainName IS NOT NULL THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END

                        ELSE UPPER(DataType) 

                                + CASE WHEN IsCharColumn = 1 OR DataType IN ('Varbinary') THEN '(' + CASE WHEN MaxLength = -1 THEN 'MAX' ELSE CAST(MaxLength AS VARCHAR(10)) END + ')' ELSE '' END 

                                + CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END 

                                + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END 

                                --+ CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END

                    END + 

                    CASE WHEN FieldID = (SELECT MAX(FieldID) FROM #ShowFields) THEN '' ELSE ',' END

        FROM #ShowFields

        

        SELECT    @sStr = @sStr + ')'

                

        INSERT    INTO #Constraints (Constraint_Type, SQL,Constraint_Name)

        VALUES    ('CREATE_TABLE', @sStr,QUOTENAME(@Schema_Name) + '.' + QUOTENAME(@sTable_Name))            

    END

    

    IF @PK_Ind = 1

    BEGIN

        -- Get Object ID of the PK

        SELECT    DISTINCT ObjectID = cco.object_id

        INTO    #PKObjectID

        FROM    sys.key_constraints            cco

                JOIN sys.index_columns        cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id

                JOIN sys.indexes            i ON cc.object_id = i.object_id AND cc.index_id = i.index_id

        WHERE    OBJECT_NAME(parent_object_id) = @sTable_Name 

        AND        i.type = 1 

        AND        is_primary_key = 1

        -- Get Object ID of the Uniques

        SELECT    DISTINCT ObjectID = cco.object_id

        INTO    #Uniques

        FROM    sys.key_constraints            cco

                JOIN sys.index_columns        cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id

                JOIN sys.indexes            i ON cc.object_id = i.object_id AND cc.index_id = i.index_id

        WHERE    OBJECT_NAME(parent_object_id) = @sTable_Name 

        AND        i.type = 2 

        AND        is_primary_key = 0 

        AND        is_unique_constraint = 1 

        INSERT    INTO #Constraints (Constraint_Type,Constraint_Name,SQL)

        SELECT    'PK_UNIQUE_CONSTRAINT',

                 Constraint_Name = cco.name,

                [PK_UNIQUE_CONSTRAINTS] = ISNULL('ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(@sTable_Name) + ' ADD CONSTRAINT ' 

                    + QUOTENAME(cco.name )

                    + CASE    type WHEN 'PK' THEN ' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN 'NONCLUSTERED ' ELSE 'CLUSTERED ' END

                                 WHEN 'UQ' THEN ' UNIQUE ' 

                     END 

                    + CASE    WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END 

                    + '(' + REVERSE(SUBSTRING(REVERSE((

                                                        SELECT    c.name + + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','

                                                        FROM    sys.key_constraints            ccok

                                                                LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id

                                                                LEFT JOIN sys.columns        c ON cc.object_id = c.object_id AND cc.column_id = c.column_id

                                                                LEFT JOIN sys.indexes        i ON cc.object_id = i.object_id AND cc.index_id = i.index_id

                                                        WHERE    i.object_id = ccok.parent_object_id 

                                                        AND        ccok.object_id = cco.object_id

                                                        FOR        XML PATH('')

                                                     )

                                                     ), 2, 8000)) + ')','')

        FROM    sys.key_constraints            cco

                INNER JOIN sys.schemas        s ON cco.schema_id = s.schema_id

                LEFT JOIN #PKObjectID        pk ON cco.object_id = pk.ObjectID

                LEFT JOIN #Uniques            u ON cco.object_id = u.objectID

        WHERE    OBJECT_NAME(cco.parent_object_id) = @sTable_Name

        AND        (type = 'PK'

        OR         type = CASE WHEN @UniqueConstraints = 1 THEN 'UQ' ELSE 'PK' END

                )

    END

    IF @FK_Ind = 1

    BEGIN

        PRINT 'Creating SQL for FK Constraints ...'

        INSERT    INTO #Constraints (Constraint_Type, Constraint_Name,SQL)

        SELECT   'FK_CONSTRAINT',

                 Constraint_Name=a.Name,

                [FK_CONSTRAINTS] = 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' WITH CHECK ADD CONSTRAINT ' + QUOTENAME(a.name) + ' FOREIGN KEY (' + a.ParentColumns + ') REFERENCES ' + QUOTENAME(a.ReferencedSchema) +'.' + QUOTENAME(a.ReferencedObject) + '(' + a.ReferencedColumns + ')'

        FROM

            (

                SELECT    fk.OBJECT_ID as object_id,

                        ReferencedSchema    = SCHEMA_NAME(o.Schema_ID),

                        ReferencedObject    = OBJECT_NAME(fk.referenced_object_id), 

                        ParentObject        = OBJECT_NAME(fk.parent_object_id),

                        Name                = fk.name,

                        ParentColumns        = REVERSE(SUBSTRING(REVERSE((

                                                    SELECT    cp.name + ','

                                                    FROM    sys.foreign_key_columns fkc

                                                            JOIN sys.columns        cp    ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id

                                                    WHERE    fkc.constraint_object_id = fk.object_id

                                                    FOR        XML PATH('')

                                                 )

                                                 ), 2, 8000)),

                        ReferencedColumns    = REVERSE(SUBSTRING(REVERSE((

                                                    SELECT    cr.name + ','

                                                    FROM    sys.foreign_key_columns fkc

                                                            JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id

                                                    WHERE    fkc.constraint_object_id = fk.object_id

                                                    FOR        XML PATH('')

                                                 )

                                                 ), 2, 8000)) 

                FROM    sys.foreign_keys fk

                        INNER JOIN sys.objects o ON fk.referenced_object_id = o.object_id

            ) a

            INNER JOIN sys.objects    co ON a.object_id = co.object_id            

            INNER JOIN sys.objects    o ON co.parent_object_id = o.object_id

            INNER JOIN sys.schemas    s ON o.schema_id = s.schema_id

        WHERE a.ParentObject = @sTable_Name

        ORDER BY a.name

    END

    IF @Check_Ind = 1

    BEGIN

        -- Create check constraints for all the columns of a table

        INSERT    INTO #Constraints (Constraint_Type,SQL)

        SELECT    'CHECK_CONSTRAINT',

                [CHECK_CONSTRAINTS] = 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' WITH CHECK ADD CHECK ' + cc.definition + ';'

        FROM    sys.check_constraints cc

                INNER JOIN sys.objects co ON cc.object_id = co.object_id

                INNER JOIN sys.objects o ON co.parent_object_id = o.object_id

                INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

        WHERE    OBJECT_NAME(cc.parent_object_id) = @sTable_Name

        ORDER    BY o.name

    END

    IF @Default_Ind = 1

    BEGIN        

        -- Create defaults for all the columns of a table

        INSERT    INTO #Constraints (Constraint_Type,SQL)

        SELECT    'DEFAULT_CONSTRAINT',

                [DEFAULT_CONSTRAINTS] = 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' ADD DEFAULT ' + sc.text + ' FOR ' + c.name

        FROM    syscomments    sc

                INNER JOIN syscolumns c ON sc.id = c.cdefault

                INNER JOIN sys.objects o ON c.id = o.object_id

                INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

        WHERE    sc.id    IN    (

                                SELECT  cdefault 

                                FROM    syscolumns

                                WHERE   id = OBJECT_ID(@Schema_Name + '.' + @sTable_Name) 

                                AND     cdefault > 0

                            )

        ORDER    BY c.name

    END

    

    SELECT    * 

    FROM    #Constraints

    WHERE    (1 = 1

    OR         Constraint_Type = CASE WHEN @Default_Ind = 1   THEN 'DEFAULT_CONSTRAINT'    ELSE '' END

    OR         Constraint_Type = CASE WHEN @Check_Ind = 1     THEN 'CHECK_CONSTRAINT'    ELSE '' END

    OR         Constraint_Type = CASE WHEN @PK_Ind = 1        THEN 'PK_UNIQUE_CONSTRAINT'   ELSE '' END

    OR         Constraint_Type = CASE WHEN @FK_Ind = 1        THEN 'FK_CONSTRAINT'          ELSE '' END

            )

    ORDER    BY ID

END

 

GO

--EXEC usp_CreateTable_DDL "表名称"