Get SQL for table structure

EXEC gettablescript ' frmuser ' CREATE PROCEDURE gettablescript@tablename VARCHAR () as--to get table scriptdeclare @Id int  , @i int, @i2 int, @Sql varchar (max), @Sql2 varchar (max), @f1 varchar (5), @f2 varchar (5), @f3 varchar (5), @f4 varchar (5), @T varchar (5) Select @Id =object_id (@TableName), @f1 = char (+) + char (ten), @f2 = ", @[email protected][email&nb Sp;protected], @f4 = ', ' + @f3 if not (@Id is null) BEGIN declare @Data table (Id int Identity primary key, D varchar    (max) not NULL, IC int NULL, RE int null, and o int not null);            --Columns with C as (select c.column_id, Nr = Row_number () over (order by c.column_id), Clr=count (*) over (), D = QuoteName (c.name) + ' + case is s.name = ' sys ' or c.is_computed=1 then ' else QuoteName (s. Name) + '. ' End + case when c.is_computed=1 then ' when s.name = ' sys ' then t.name else QuoteName (t.name) End + case when c.user_type_id!=c.system_type_id or c.is_computed=1stEn ' When t.name in (' xml ', ' uniqueidentifier ', ' tinyint ', ' timestamp ', ' time ', ' text ', ' sysname ', ' sq ' L_variant ', ' smallmoney ', ' smallint ', ' smalldatetime ', ' ntext ', ' money ', ' int ', ' image ' , ' HierarchyID ', ' Geometry ', ' geography ', ' float ', ' datetimeoffset ', ' datetime2 ', ' datetime ', ' Date ', ' bigint ', ' bit ') th                        En ' When t.name in (' varchar ', ' varbinary ', ' real ', ' numeric ', ' decimal ', ' char ', ' binary ') Then ' + isnull (CONVERT (Varchar,nullif (c.max_length,-1), ' Max ') + isnull (', ' +convert (Varchar,nullif (C.scale , 0)) (') + ') ' When t.name in (' nvarchar ', ' nchar ') Then ' (' + isnull (CONVERT (VA                    Rchar,nullif (c.max_length,-1)/2), ' Max ') + isnull (', ' +convert (Varchar,nullif (c.scale, 0)), ') + ') '                    Else '?? ' End + Case when ic.object_id are not null then ' identity (' + CONVERT (varchar,ic.seed_value) + ', ' + conVert (Varchar,ic.increment_value) + ') ' Else ' end + case-c.is_computed=1 Then ' as ' + cc.definition WH En c.is_nullable = 1 Then ' null ' else ' is null ' end + case C.is_rowguidcol when 1 Then ' ROWGUIDCOL ' els E "End + case" d.object_id is not null and then ' default ' + d.definition Else "End from SYS.C  Olumns c INNER join sys.types t on t.user_type_id = c.user_type_id INNER JOIN Sys.schemas s on s.schema_id=t.schema_id LEFT OUTER join Sys.computed_columns cc on cc.object_id=c.object_id and Cc.column_i d=c.column_id left outer joins sys.default_constraints D on [email protected] and D.PARENT_COLUMN_ID=C.C        olumn_id LEFT OUTER join Sys.identity_columns IC on ic.object_id=c.object_id and ic.column_id=c.column_id where [email protected]) insert INTO @Data (d, O) select "+ D + case Nr" when CLR then "   Else ', ' + @f1 end, 0     From c where isn't D is NULL order by column_id--subobjects set @i=0 while 1=1 begin SE        Lect top 1 @i=c.object_id, @T = C.type, @i2 =i.index_id from sys.objects C left outer join sys.indexes I On [email protected] and I.name=c.name where [email protected] and c.object_id>@i and C.type not in            (' D ') Order by c.object_id if @ @rowcount =0 breaks if @T = ' C ' insert into @Data Select @f4 + ' check ' + case is_not_for_replication if 1 then ' Not FOR replication ' else ' end + definition, NULL, nul L, sys.check_constraints where [email protected] else if @T = ' Pk ' INSERT INTO @            Data Select @f4 + ' primary key ' + isnull (' + nullif (lower (I.TYPE_DESC), ' clustered '), '), @i2, NULL, 20            From sys.indexes i where [email protected] and [email protected] else if @T = ' UQ ' INSERT INTO @DataVALUES (@f4 + ' unique ', @i2, NULL, +) ELSE if @T = ' F ' begin insert into @Data sel            ECT @f4 + ' foreign key ',-1, @i, Sys.foreign_keys f where [email protected] Insert INTO @Data select ' References ' + QuoteName (s.name) + '. ' + QuoteName (o.name),-2, @i, $ fr Om Sys.foreign_keys f inner join sys.objects O on o.object_id=f.referenced_object_id inne R Join Sys.schemas s on s.schema_id=o.schema_id where [email protected] insert INTO @Data Select ' Not FOR replication ', -3, @i, Sys.foreign_keys f INNER JOIN SYS.OB Jects o o.object_id=f.referenced_object_id inner join Sys.schemas s on S.schema_id=o.s chema_id where [email protected] and f.is_not_for_replication=1 end else Inse RT into @Data values (@f4 + ' Unknow subobject [' + @T + '] ', NULL, NULL,) end insert into @Data values (@f1 + ') ', NULL, NULL, 100)-- Indexes INSERT INTO @Data select @f1 + ' create ' + case Is_unique if 1 then ' unique ' else ' end + lower (s.type_ DESC) + ' index ' + ' I ' + convert (varchar, row_number () over (order by index_id)) + ' on ' + QuoteName (SC. Name) + '. ' + QuoteName (o.name), index_id, NULL, sys.indexes s inner join sys.objects o on o.object_id= s.object_id INNER JOIN Sys.schemas SC on sc.schema_id=o.schema_id where [email protected] and Is_unique_cons Traint=0 and is_primary_key=0 and s.type_desc! = ' heap '--columns set @i=0 while 1=1 begin select        Top 1 @i=ic from @Data where ic>@i ORDER by ICS if @ @rowcount =0 break Select @i2 =0, @Sql =null, @Sql2 =null While 1=1 begin select @i2 =index_column_id, @Sql = Case C.is_included_column W Hen 1 Then @Sql else IsNull (@Sql + ', ',' (') + CC. Name + Case C.is_descending_key if 1 Then ' desc ' Else ' end end, @Sql2 = Case C.is_included_column whe N 0 Then @Sql2 else isnull (@Sql2 + ', ', ' (') + CC. Name + Case C.is_descending_key if 1 Then ' desc ' Else ' end end from Sys.index_columns c inner Join Sys.columns cc on c.column_id=cc.column_id and cc.object_id=c.object_id where [email prote  CTED] and [email protected] and index_column_id> @i2 ORDER by index_column_id if @ @rowcount =0 Break End Update @Data set [email protected] + ') ' + isnull (' include ' + @Sql2 + ') ', ') Where [em  Ail protected] End--references set @i=0 while 1=1 begin select top 1 @i=re from @Data            where re>@i order by re if @ @rowcount =0 breaks select @i2 =0, @Sql =null, @Sql2 =null while 1=1 Begin select @i2 =f.constraint_column_id, @Sql = IsnulL (@Sql + ', ', ' (') + C1. Name, @Sql2 = isnull (@Sql2 + ', ', ' (') + c2. Name from Sys.foreign_key_columns f INNER join sys.columns C1 on C1.column_id=f.parent_co LUMN_ID and c1.object_id=f.parent_object_id inner join sys.columns C2 on C2.column_id=f.referenced_c             olumn_id and c2.object_id=f.referenced_object_id where [email protected] and f.constraint_column_id> @i2 ORDER by f.constraint_column_id if @ @rowcount =0 break End update @Data Set d = d + @Sql + ') ' WHERE [email protected] and ic=-1 update @Data Set d = d + @Sql2 + ') ' WHERE [Email protec    Ted] and Ic=-2 end; --Render with X as (select Id=d.id-1, D=d.d + isnull (D2). D, ') from @Data D left outer join @Data D2 on D.re=d2.re and d2.o=42 where d.o=41) upd Ate @Data set D=D.D+X.D from @Data D inner join X on x.id=d.id deleTe @Data where O in (A, a) select @Sql = ' CREATE table ' + QuoteName (s.name) + '. ' + QuoteName (o.name) + ' (' + @f1     From sys.objects o inner join sys.schemas s on o.schema_id = s.schema_id where [email protected] set @i=0 While 1=1 the begin select top 1 @i=id, @Sql = @Sql + D from @Data ORDER by O, case is o=0 then right (' 000        0 ' + convert (varchar,id), 5) Else D end, id if @ @rowcount =0 break Delete @Data where [email protected] End End SELECT @Sql

Get SQL for table structure