EXEC GetTableScript 'frmuser'
CREATE PROCEDURE GetTableScript
@TableName VARCHAR(50)
AS
--To get table script
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(@TableName), @f1 = char(13) + char(10), @f2 = ' ', @f3=@f1+@f2, @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, 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', '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(varchar,nullif(c.max_length,-1) / 2), '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 where NOT D IS NULL
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
END
SELECT @Sql
引自：http://www.cnblogs.com/unruledboy/archive/2011/12/05/SQLMon2.html