SQL SERVER 生成表结构的语句
来源:互联网 发布:拍婚纱照攻略知乎 编辑:程序博客网 时间:2024/05/22 03:25
--获取表结构创建脚本 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.ColID WHERE a.[ID] = @ObjectID ORDER BY a.ColOrder --插入主键和索引 DECLARE @IndexID int, @IndexScript nvarchar(4000) DECLARE IndexCursor CURSOR FOR SELECT b.object_id, ' Constraint ' + a.Name + 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 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 + INDEX_COL(object_name(OBJECT_ID), 2, 1) + ', ' FROM sys.index_columns WHERE Object_ID = @IndexID --AND Index_ID = 2 SET @IndexScript = LEFT(@IndexScript, LEN(@IndexScript) - 1) + '),' INSERT INTO @TableScript(ScriptLine) VALUES(@IndexScript) FETCH NEXT FROM IndexCursor INTO @IndexID, @IndexScript END CLOSE IndexCursor DEALLOCATE IndexCursor --除去最后一个,号 UPDATE @TableScript SET 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''' + a.Name + 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''' + b.Name + N'''' FROM sys.extended_properties a LEFT JOIN sys.objects b ON b.[Object_ID] = a.major_Id WHERE a.major_Id = @ObjectID AND Minor_ID = 0 INSERT INTO @TableScript(ScriptLine) VALUES('GO') DECLARE PropCursor CURSOR FOR SELECT N'EXEC sys.sp_addextendedproperty @name = N''' + a.Name + 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''' + b.Name + N'''' + N', @level2type = N''COLUMN'', @level2name = N''' + c.[Name] + N'''' FROM sys.extended_properties a LEFT JOIN sys.objects b ON b.[Object_ID] = a.major_Id LEFT JOIN sys.syscolumns c ON c.[ID] = a.major_Id AND c.ColID = a.Minor_ID WHERE a.major_Id = @ObjectID AND Minor_ID <> 0 OPEN PropCursor FETCH NEXT FROM PropCursor INTO @PropScript WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO @TableScript(ScriptLine) VALUES(@PropScript) INSERT INTO @TableScript(ScriptLine) VALUES(N'GO') FETCH NEXT FROM PropCursor INTO @PropScript END CLOSE PropCursor DEALLOCATE PropCursor INSERT 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 @ConstIDTable SELECT ConstID FROM sys.sysreferences WHERE FKeyID = @ObjectID OR RKeyID = @ObjectID ORDER BY FKeyID WHILE EXISTS(SELECT * FROM @ConstIDTable) BEGIN SELECT TOP 1 @ConstID = ConstID FROM @ConstIDTable DELETE FROM @ConstIDTable WHERE ConstID = @ConstID INSERT INTO @TableScript SELECT N'--ALTER TABLE [dbo].[' + object_name(FKeyID) + '] WITH CHECK' FROM sys.sysreferences WHERE ConstID = @ConstID INSERT INTO @TableScript(ScriptLine) VALUES('--ADD' + CHAR(13) + CHAR(10)) SELECT @ForeignLine = N'--Constraint ' + object_name(ConstID) + ' Foreign Key(', @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 = KeyCnt FROM sys.sysreferences a LEFT JOIN sys.foreign_keys b ON a.ConstID = b.Object_ID WHERE a.ConstID = @ConstID --取字段 SET @i = 1 WHILE @i <= @keyCnt BEGIN SET @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 = @ConstID print @SQLScript SET @ForeignLine = @ForeignLine + CASE WHEN @i > 1 THEN ', ' ELSE '' END + @FColName SET @ReferencesLine = @ReferencesLine + CASE WHEN @i > 1 THEN ', ' ELSE '' END + @RColName SET @i = @i + 1 END INSERT 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 @TableScript SET NOCOUNT OFF
0 0
- SQL SERVER 生成表结构的语句
- SQL Server复制表结构和表数据生成新表的语句
- SQL语句生成表结构
- SQL SERVER 2000获取表结构的SQL语句
- SQL SERVER 获取表结构信息的SQL语句
- sql server 表结构数据字典的sql语句
- SQL SERVER 获取表结构信息的SQL语句
- SQL SERVER 获取表结构信息的SQL语句
- Sql server 得到表结构的Sql语句
- SQL SERVER 查询表结构的SQL语句
- 自动生成表结构 sql 从已知数据库获得创建表的结构 sql server
- MYSQL使用SQL语句生成表结构文档语句
- SQL语句生成SQL Server数据库的数据字典
- 超级有用的SQL语句(分析SQL SERVER 数据库表结构专用)
- MS SQL Server树型结构数据显示的SQL语句(纯SQL语句,不用函数)
- SQL语句实现SQL Server及ACCESS复制表结构或表结构及内容到新的表
- SQL语句实现SQL Server及ACCESS复制表结构或表结构及内容到新的表
- oracle中生成树型结构的sql语句
- 为android系统添加产品的过程
- 字符转整型
- 存储过程,存储过程循环,一张表整列插入另一张表
- PC-Lint详解
- 探索 Pexpect,第 1 部分:剖析 Pexpect
- SQL SERVER 生成表结构的语句
- Ubuntu Server 将环境变量修改为英文,防止中文乱码
- 一维整型字符串数组、二维数组、锯齿数组
- 来自19位科技大亨的励志箴言(绝对值得收藏)
- sqlserver 取10到20条
- Objective-C学习笔记二:面向对象概述
- swift开发
- 探索 Pexpect,第 2 部分:Pexpect 的实例分析
- Android-编程小问题记录