--获取表结构创建脚本
Alter Proc sp_SYS_CreateTableSQL_Struct(@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 'Create Table ' + USER_NAME(OBJECTPROPERTY(@ObjectID, 'OwnerId')) + '.' + object_name(@ObjectID) + '(' --插入字段 INSERT INTO @TableScript(ScriptLine) SELECT ' ' + a.Name + ' ' + b.name + '' + 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 d.Name IS NOT NULL THEN ' Default' + d.Definition ELSE '' END + ',' 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.ColI
SQL SERVER 生成表结构的语句
最新推荐文章于 2024-07-05 19:26:50 发布
这个博客提供了在SQL Server中生成表结构创建语句的存储过程。通过调用sp_SYS_CreateTableSQL_Struct存储过程,可以获取指定表的创建脚本,包括表的字段、类型、主键、索引、外键和默认约束等详细信息。
摘要由CSDN通过智能技术生成