获取表结构创建脚本

--获取表结构创建脚本Alter Proc sp_SYS_CreateTableSQL_Struct(@TableName sysname)ASSET NOCOUNT ONDECLARE @ObjectID intDECLARE @TableScript Table(Iden Int IDENTITY(1, 1) ,ScriptLine nvarchar(4000)) SET @ObjectID = object_id(@TableName)IF @ObjectID IS NULL OR OBJECTPROPERTY(@ObjectID, 'IsTable') = 0BEGINRAISERROR('指定的对象不是表对象', 16, 1)RETURNEND--获取表的创建脚本--插入表头INSERT INTO @TableScript(ScriptLine)SELECT 'Create Table ' + USER_NAME(OBJECTPROPERTY(@ObjectID, 'OwnerId')) + '.'+ object_name(@ObjectID) + '('--插入字段INSERT INTO @TableScript(ScriptLine)SELECT ' ' + + ' ' + + '' +CASE WHEN c.Object_id IS NOT NULL THEN ' Identity(' +CONVERT(nvarchar, c.seed_value) + ', ' + CONVERT(nvarchar, c.increment_value) + ')'ELSE '' END +CASE WHEN b.xusertype IN (167, 175, 231, 239) THEN '(' + CONVERT(nvarchar, a.prec) + ')'WHEN b.xusertype in (106, 108) THEN '(' + CONVERT(nvarchar, a.xprec) + ', ' + CONVERT(nvarchar, a.xscale) + ')'ELSE '' END +CASE a.isnullable WHEN 1 THEN '' ELSE ' Not' END + ' Null' +CASE WHEN IS NOT NULL THEN ' Default' + d.Definition ELSE'' END +','FROM sys.syscolumns aLEFT JOIN sys.systypes b ON a.xusertype = b.xusertypeLEFT JOIN sys.identity_columns c ON c.Object_id = a.ID AND c.Column_ID = a.ColIDLEFT JOIN sys.default_constraints d ON d.Parent_Object_ID = a.ID ANDd.Parent_column_ID = a.ColIDWHERE a.[ID] = @ObjectIDORDER BY a.ColOrder--插入主键和索引DECLARE @IndexID int, @IndexScript nvarchar(4000)DECLARE IndexCursor CURSOR FORSELECT b.object_id, ' Constraint ' + + N' ' +CASE a.Type WHEN 'PK' THEN 'Primary Key ' WHEN 'UQ' THEN 'UNIQUE ' END + CASE b.Type WHEN 1 THEN 'CLUSTERED' WHEN 2 THEN 'NONCLUSTERED ' END + '(' FROM sys.key_constraints aLEFT JOIN sys.indexes b ON b.Object_ID = a.Parent_Object_ID AND b.index_id= a.unique_index_idWHERE a.Parent_Object_ID = @ObjectIDOPEN IndexCursorFETCH NEXT FROM IndexCursor INTO @IndexID, @IndexScriptWHILE @@FETCH_STATUS = 0BEGINSELECT @IndexScript = @IndexScript + INDEX_COL(object_name(OBJECT_ID), 2,1) + ', 'FROM sys.index_columnsWHERE Object_ID = @IndexID --AND Index_ID = 2SET @IndexScript = LEFT(@IndexScript, LEN(@IndexScript) - 1) + '),'INSERT INTO @TableScript(ScriptLine) VALUES(@IndexScript)FETCH NEXT FROM IndexCursor INTO @IndexID, @IndexScriptENDCLOSE IndexCursorDEALLOCATE IndexCursor--除去最后一个,号UPDATE @TableScriptSET ScriptLine = LEFT(ScriptLine, LEN(ScriptLine) - 1)WHERE Iden = (SELECT MAX(Iden) FROM @TableScript)INSERT INTO @TableScript(ScriptLine) VALUES(')')INSERT INTO @TableScript(ScriptLine) VALUES('GO')INSERT INTO @TableScript(ScriptLine) VALUES('')--获取表备注(表字段说明/表说明)/*DECLARE @PropScript nvarchar(4000)INSERT INTO @TableScript(ScriptLine)SELECT N'EXEC sys.sp_addextendedproperty @name = N''' + + N''', @value= N''' + CONVERT(nvarchar, a.Value)+ N''', @level0type = N''SCHEMA'', @level0name = N''' +USER_NAME(OBJECTPROPERTY(a.major_Id, N'OwnerId'))+ N''', @level1type = N''TABLE'', @level1name = N''' + + N'''' FROM sys.extended_properties aLEFT JOIN sys.objects b ON b.[Object_ID] = a.major_IdWHERE a.major_Id = @ObjectIDAND Minor_ID = 0INSERT INTO @TableScript(ScriptLine) VALUES('GO')DECLARE PropCursor CURSOR FORSELECT N'EXEC sys.sp_addextendedproperty @name = N''' + + N''', @value= N''' + CONVERT(nvarchar, a.Value)+ N''', @level0type = N''SCHEMA'', @level0name = N''' +USER_NAME(OBJECTPROPERTY(a.major_Id, N'OwnerId'))+ N''', @level1type = N''TABLE'', @level1name = N''' + + N''''+ N', @level2type = N''COLUMN'', @level2name = N''' + c.[Name] + N'''' FROM sys.extended_properties aLEFT JOIN sys.objects b ON b.[Object_ID] = a.major_IdLEFT JOIN sys.syscolumns c ON c.[ID] = a.major_Id AND c.ColID = a.Minor_ID WHERE a.major_Id = @ObjectID AND Minor_ID <> 0OPEN PropCursorFETCH NEXT FROM PropCursor INTO @PropScriptWHILE @@FETCH_STATUS = 0BEGININSERT INTO @TableScript(ScriptLine) VALUES(@PropScript)INSERT INTO @TableScript(ScriptLine) VALUES(N'GO')FETCH NEXT FROM PropCursor INTO @PropScriptENDCLOSE PropCursorDEALLOCATE PropCursorINSERT INTO @TableScript(ScriptLine) VALUES('')*/--获取表外键DECLARE @ConstID int, @i tinyint, @keyCnt tinyint, @TempletSQL nvarchar(400),@SQLScript nvarchar(500),@FColName sysname, @RColName sysname,@ForeignLine nvarchar(4000), @ReferencesLine nvarchar(4000),@ReferencesAction nvarchar(4000)DECLARE @ConstIDTable table(ConstID int)SELECT @FColName = '', @RColName = '',@TempletSQL = N'SELECT @eFColName = ''['' + col_name(FkeyID, Fkey%d) +'']'', @eRColName = ''['' '+ N' + col_name(RkeyID, Rkey%d) + '']'' FROM sys.sysreferences WHERE ConstID = @ConstID 'INSERT INTO @ConstIDTableSELECT ConstID FROM sys.sysreferences WHERE FKeyID = @ObjectID OR RKeyID =@ObjectID ORDER BY FKeyIDWHILE EXISTS(SELECT * FROM @ConstIDTable)BEGINSELECT TOP 1 @ConstID = ConstID FROM @ConstIDTableDELETE FROM @ConstIDTable WHERE ConstID = @ConstIDINSERT INTO @TableScriptSELECT N'--ALTER TABLE [dbo].[' + object_name(FKeyID) + '] WITH CHECK'FROM sys.sysreferencesWHERE ConstID = @ConstIDINSERT INTO @TableScript(ScriptLine) VALUES('--ADD' + CHAR(13) + CHAR(10))SELECT @ForeignLine = N'--Constraint ' + object_name(ConstID) + ' ForeignKey(',@ReferencesLine = N'REFERENCES [dbo].[' + object_name(RKeyID) + '] (',@ReferencesAction = CASE b.Delete_Referential_Action WHEN 0 THEN N''WHEN 1 THEN N'ON DELETE Cascade'WHEN 2 THEN N'ON DELETE SET NULL'WHEN 3 THEN N'ON DELETE SET DEFAULT'END + ' ' +CASE b.Delete_Referential_Action WHEN 0 THEN N''WHEN 1 THEN N'ON UPDATE Cascade'WHEN 2 THEN N'ON UPDATE SET NULL'WHEN 3 THEN N'ON UPDATE SET DEFAULT'END,@keyCnt = KeyCntFROM sys.sysreferences aLEFT JOIN sys.foreign_keys b ON a.ConstID = b.Object_IDWHERE a.ConstID = @ConstID--取字段SET @i = 1WHILE @i <= @keyCntBEGINSET @SQLScript = REPLACE(@TempletSQL, '%d', CONVERT(nvarchar, @i))EXEC sp_executesql @stmt = @SQLScript, @params = N'@eFColName sysname output, @eRColName sysname output, @ConstID int',@eFColName = @FColName output, @eRColName =@RColName output, @ConstID = @ConstIDprint @SQLScriptSET @ForeignLine = @ForeignLine + CASE WHEN @i > 1 THEN ', ' ELSE '' END + @FColNameSET @ReferencesLine = @ReferencesLine + CASE WHEN @i > 1 THEN ', ' ELSE '' END + @RColNameSET @i = @i + 1ENDINSERT INTO @TableScript(ScriptLine) VALUES(@ForeignLine + N') ' +@ReferencesLine + N')')IF @ReferencesAction <> ''INSERT INTO @TableScript(ScriptLine) VALUES(@ReferencesAction)INSERT INTO @TableScript(ScriptLine) VALUES(N'GO')END--返回表的创建脚本SELECT ScriptLine FROM @TableScriptSET NOCOUNT OFF。