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 "表名称"
