导出创建表脚本的存储过程

转：导出创建表脚本的存储过程
-----------获取当前数据库所有表的外键创建脚本、获取指定表的创建脚本,包括表和字段的属性、外键-----------------------


CREATEPROCEDURE[dbo].[sp_HelpTable](@TableName sysname)
AS
SET NOCOUNT ON

intIDENTITY(1, 1), ScriptLine nvarchar(4000))
)
, 'IsTable') =0
BEGIN
RAISERROR('指定的对象不是表对象', 16, 1)
RETURN
END
--获取表的创建脚本
--插入表头
)
SELECT N'CREATE TABLE ['+USER_NAME(OBJECTPROPERTY(@ObjectID, N'OwnerId')) + N'].['+object_name(@ObjectID) + N']('

--插入字段
)
SELECT N' ['+ a.Name + N'] ['+ b.name + N']'+
CASEWHEN c.Object_idISNOTNULLTHEN N' IDENTITY('+CONVERT(nvarchar, c.seed_value) + N', '+CONVERT(nvarchar, c.increment_value) + N')'
ELSE''END+
CASEWHEN b.xusertype IN (167, 175, 231, 239) THEN N'('+CONVERT(nvarchar, a.prec) + N')'
WHEN b.xusertype in (106, 108) THEN N'('+CONVERT(nvarchar, a.xprec) + N', '+CONVERT(nvarchar, a.xscale) + N')'
ELSE''END+
CASE a.isnullable WHEN1THEN N''ELSE N' NOT'END+ N' NULL'+
CASEWHEN d.Name ISNOTNULLTHEN N' DEFAULT '+ d.Definition ELSE N''END+
 N','
FROM sys.syscolumns a
LEFTJOIN sys.systypes b ON a.xusertype = b.xusertype
LEFTJOIN sys.identity_columns c ON c.Object_id= a.ID AND c.Column_ID = a.ColID
LEFTJOIN sys.default_constraints d ON d.Parent_Object_ID = a.ID AND d.Parent_column_ID = a.ColID
WHERE a.[ID]=@ObjectID
ORDERBY a.ColOrder

--插入主键和索引
, @IndexScriptnvarchar(4000)
DECLARE IndexCursor CURSORFOR
SELECT b.Index_ID, N' CONSTRAINT ['+ a.Name + N'] '+
CASE a.Type WHEN'PK'THEN N'PRIMARY KEY 'WHEN'UQ'THEN N'UNIQUE 'END+
CASE b.Type WHEN1THEN N'CLUSTERED'WHEN2THEN N'NONCLUSTERED 'END+ N'('
FROM sys.key_constraints a
LEFTJOIN sys.indexes b ON b.Object_ID= a.Parent_Object_ID AND b.index_id = a.unique_index_id
WHERE a.Parent_Object_ID =@ObjectID
OPEN IndexCursor
FETCHNEXTFROM IndexCursor INTO@IndexID, @IndexScript

BEGIN
+ N'['+INDEX_COL(object_name(@ObjectID), 2 , 1) + N'],'
FROM sys.index_columns 

AND Index_ID =2

, LEN(@IndexScript) -1) + N'),'
) VALUES(@IndexScript)
FETCHNEXTFROM IndexCursor INTO@IndexID, @IndexScript
END
Close IndexCursor
DEALLOCATE IndexCursor

) VALUES(')')