USE [WMP]

GO

/****** Object: StoredProcedure [dbo].[sp_HelpTable] Script Date: 2016/1/3 23:44:16 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--****************************************************************************

-- 软件名称: May Flower Erp

-- 版权所有: (C) 2005-2006 May Flower ERP 开发组

-- 功能描述: 获取指定表的创建脚本,包括表和字段的属性、外键(注释掉的)

------------------------------------------------------------------------------

-- 参数列表:

-- 1: @TableName 需要创建脚本的表的名称

--****************************************************************************

--|</PRE>*/

ALTER PROCEDURE [dbo].[sp_HelpTable](@TableName sysname)

AS

SET NOCOUNT ON

DECLARE @ObjectID int

DECLARE @TableScript table(Iden int IDENTITY(1, 1), ScriptLine nvarchar(4000))

SET @ObjectID = object_id(@TableName)

IF @ObjectID IS NULL OR OBJECTPROPERTY(@ObjectID, 'IsTable') = 0

BEGIN

RAISERROR('指定的对象不是表对象', 16, 1)

RETURN

END

--获取表的创建脚本

--插入表头

INSERT INTO @TableScript(ScriptLine)

SELECT N'CREATE TABLE [' + USER_NAME(OBJECTPROPERTY(@ObjectID, N'OwnerId')) + N'].[' + object_name(@ObjectID) + N']('

--插入字段

INSERT INTO @TableScript(ScriptLine)

SELECT N' [' + a.Name + N'] [' + b.name + N']' +

CASE WHEN c.Object_id IS NOT NULL THEN N' IDENTITY(' + CONVERT(nvarchar, c.seed_value) + N', ' + CONVERT(nvarchar, c.increment_value) + N')'

ELSE '' END +

CASE WHEN 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 WHEN 1 THEN N'' ELSE N' NOT' END + N' NULL' +

CASE WHEN d.Name IS NOT NULL THEN N' DEFAULT ' + d.Definition ELSE N'' END +

N','

FROM sys.syscolumns a

LEFT JOIN sys.systypes b ON a.xusertype = b.xusertype

LEFT JOIN sys.identity_columns c ON c.Object_id = a.ID AND c.Column_ID = a.ColID

LEFT JOIN sys.default_constraints d ON d.Parent_Object_ID = a.ID AND d.Parent_column_ID = a.ColID

WHERE a.[ID] = @ObjectID

ORDER BY a.ColOrder

--插入主键和索引

DECLARE @IndexID int, @IndexScript nvarchar(4000)

DECLARE IndexCursor CURSOR FOR

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 WHEN 1 THEN N'CLUSTERED' WHEN 2 THEN N'NONCLUSTERED ' END + N'('

FROM sys.key_constraints a

LEFT JOIN 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

FETCH NEXT FROM IndexCursor INTO @IndexID, @IndexScript

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @IndexScript = @IndexScript + N'[' + INDEX_COL(object_name(@ObjectID), 2 , 1) + N'],'

FROM sys.index_columns

WHERE Object_ID = @ObjectID

AND Index_ID = 2

SET @IndexScript = LEFT(@IndexScript, LEN(@IndexScript) -1) + N'),'

INSERT INTO @TableScript(ScriptLine) VALUES(@IndexScript)

FETCH NEXT FROM IndexCursor INTO @IndexID, @IndexScript

END

Close IndexCursor

DEALLOCATE IndexCursor

insert into @TableScript(ScriptLine) VALUES(')')

select * from @TableScript

 

--本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/10/14/4669511.aspx