If object_id (' Sp_create_table_sql ', ' P ') is not nulldrop proc sp_create_table_sqlgocreate proc Sp_create_table_sql (@ TableName varchar (255)) as Begin--exec sp_create_table_sql ' ad_adgroup '--0. Hiro--1. Indexes that do not support non-primary key classes--2. Non-default ordering of the primary key is not supported--3. Default--4 is not supported. Computed columns are not supported--5. To complete declare @sql_create varchar (max) = ';d eclare @sql_column varchar (max);d eclare @sql_primary varchar (max), with CTE as ( Select QUOTENAME (c.name) + ' +type_name (c.system_type_id) + ' +case when Type_name (c.system_type_id) in (' Char ', ' varch Ar ', ' decimal ') Then ' (' Else ' End +case when Type_name (c.system_type_id) in (' char ', ' varchar ', ' nvarchar ') the N Cast (max_length as varchar) Else ' end+case when Type_name (c.system_type_id) in (' decimal ') then cast (C.precisi on as varchar) + ', ' +cast (c.scale as varchar) Else ' end+ case when Type_name (c.system_type_id) in (' char ', ' varchar ', ' Decimal ') Then ', ' Else ' end +case when c.is_nullable = 1 Then ' null ' else ' is null ' end +case when c.is_iDentity = 0 Then ' Else ' identity ' end Sqlstr, column_id from sys.objects as O join Sys.columns as C on o.object_id = c.object_id where o.name = @tablename and O.type = ' U ') Select @sql_column = Stuff ((select ', ' + Sqlstr + CHAR (Ten) FR Om CTE ORDER BY column_id ASC FOR XML Path ("),"), "Select @sql_primary = Stuff ((select ', ' + c.name from Sys.inde X_columns as I join Sys.indexes as IX on i.object_id = ix.object_id and i.index_id = ix.index_id join sys.columns as C o n i.object_id = c.object_id and i.column_id = c.column_id where object_name (i.object_id) = @tablename and Ix.is_primary_k EY = 1 ORDER by I.key_ordinal FOR XML Path (")," ") Set @sql_create = ' CREATE table ' + @tablename + ' (' + @sq L_column + case If Len (@sql_primary) >= 1 Then (', primary key (' + @sql_primary + ') ') ' Else ' end + ') ' pri NT '--@sql_create--' Print @sql_createend
MSSQL returns the CREATE statement for the table