RL_ScriptTable

SQL Server function to generate table creation script.

CREATE function [dbo].[RL_ScriptTable](@Object varchar(255))

returns varchar(max) as begin

declare @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(@Object), @f1 = char(13) + char(10), @f2 = '	', @f3=@f1+@f2, @f4=',' + @f3

if @Id is null return null

declare @Data table(Id int identity primary key, D varchar(max) not null, ic int null, re int null, 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 when 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=1 then ''

when t.Name in ('xml', 'uniqueidentifier', 'tinyint', 'timestamp', 'time', 'text', 'sysname', 'sql_variant', 'smallmoney', 'smallint', 'smalldatetime', 'ntext', 'money',

'int', 'image', 'hierarchyid', 'geometry', 'geography', 'float', 'datetimeoffset', 'datetime2', 'datetime', 'date', 'bigint', 'bit') then ''

when t.Name in('varchar','varbinary', 'real', 'nvarchar', 'numeric', 'nchar', 'decimal', 'char', 'binary')

then '(' + isnull(convert(varchar,nullif(c.max_length,-1)), 'max') + isnull(','+convert(varchar,nullif(c.scale, 0)), '') + ')'

else '??'

end +

case when ic.object_id is not null then ' identity(' + convert(varchar,ic.seed_value) + ',' + convert(varchar,ic.increment_value) + ')' else '' end +

case when c.is_computed=1 then 'as' + cc.definition when c.is_nullable = 1 then ' null' else ' not null' end +

case c.is_rowguidcol when 1 then ' rowguidcol' else '' end +

case when d.object_id is not null then ' default ' + d.definition else  '' end

from sys.columns 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_id=c.column_id

left outer join sys.default_constraints d

on d.parent_object_id=@id and d.parent_column_id=c.column_id

left outer join sys.identity_columns ic

on ic.object_id=c.object_id and ic.column_id=c.column_id

where c.object_id=@Id

)

insert into @Data(D, o)

select '	' + D + case Nr when Clr then '' else ',' + @f1 end, 0

from c

order by column_id

-- SubObjects

set @i=0

while 1=1

begin

select top 1 @i=c.object_id, @T = c.type, @i2=i.index_id

from sys.objects c

left outer join sys.indexes i

on i.object_id=@Id and i.name=c.name

where parent_object_id=@Id and c.object_id>@i and c.type not in('D')

order by c.object_id

if @@rowcount=0 break

if @T = 'C'

insert into @Data

select @f4 + 'check ' + case is_not_for_replication when 1 then 'not for replication ' else '' end + definition, null, null, 10

from sys.check_constraints where object_id=@i

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 i.object_id=@Id and i.index_id=@i2

else if @T = 'uq'

insert into @Data values(@f4 + 'unique', @i2, null, 30)

else if @T = 'f'

begin

insert into @Data

select @f4 + 'foreign key', -1, @i, 40

from sys.foreign_keys f

where f.object_id=@i

insert into @Data

select ' references ' + quotename(s.name) + '.' + quotename(o.name), -2, @i, 41

from sys.foreign_keys f

inner join sys.objects o

on o.object_id=f.referenced_object_id

inner join sys.schemas s

on s.schema_id=o.schema_id

where f.object_id=@i

insert into @Data

select ' not for replication', -3, @i, 42

from sys.foreign_keys f

inner join sys.objects o

on o.object_id=f.referenced_object_id

inner join sys.schemas s

on s.schema_id=o.schema_id

where f.object_id=@i and f.is_not_for_replication=1

end

else

insert into @Data values(@f4 + 'Unknow SubObject [' + @T + ']', null, null, 99)

end

insert into @Data values(@f1+')', null, null, 100)

-- Indexes

insert into @Data

select @f1 + 'create ' + case is_unique when 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, 1000

from 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 s.object_id=@Id and is_unique_constraint=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 ic

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 when 1 then @Sql else isnull(@Sql + ', ', '(') + cc.Name + case c.is_descending_key when 1  then ' desc' else '' end end,

@Sql2 = case c.is_included_column when 0 then @Sql2 else isnull(@Sql2 + ', ', '(') + cc.Name + case c.is_descending_key when 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 c.object_id=@Id and index_id=@i and index_column_id>@i2

order by index_column_id

if @@rowcount=0 break

end

update @Data set D=D+@Sql +')' + isnull(' include' + @Sql2 + ')', '') where ic=@i

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 break

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_column_id and c1.object_id=f.parent_object_id

inner join sys.columns c2

on c2.column_id=f.referenced_column_id and c2.object_id=f.referenced_object_id

where f.constraint_object_id=@i and f.constraint_column_id>@i2

order by f.constraint_column_id

if @@rowcount=0 break

end

update @Data set D = D + @Sql + ')'  where re=@i and ic=-1

update @Data set D = D + @Sql2 + ')'  where re=@i 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

)

update @Data

set D=d.D+x.D

from @Data d

inner join x

on x.id=d.id

delete @Data where o in(41, 42)

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 o.object_id=@Id

set @i=0

while 1=1

begin

select top 1 @I=Id, @Sql = @Sql + D from @Data order by o, case when o=0 then right('0000' + convert(varchar,id),5)  else D end, id

if @@rowcount=0 break

delete @Data where id=@i

end

return @Sql

end