Sql Server 导出对象的脚本

----获取表格脚本

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
declare @count int
declare @tableName nvarchar(128)
declare @columnName nvarchar(128)
declare @columnLength smallint
declare @isnullable int
declare @typeName nvarchar(128)
declare @autoval nvarchar(128)
declare @oldTableName nvarchar(128)
declare @sqlString nvarchar(4000)
declare @columnList nvarchar(4000)
declare @identityString nvarchar(128)
select @oldTableName = ''
select @columnList =''
create table #tmpTable (sql ntext)
DECLARE myCursor  CURSOR FOR
SELECT a.name as tableName, b.name as columnName, b.Length as columnLength, b.isnullable, c.name as typeName, b.autoval
from sysobjects a, syscolumns b, systypes c 
where a.xtype ='U'  and a.status >0 and a.id = b.id and b.xtype = c.xtype
OPEN myCursor
FETCH NEXT FROM myCursor into @tableName,@columnName,@columnLength,@isnullable,@typeName,@autoval
WHILE @@FETCH_STATUS = 0
BEGIN
 if @tableName <> @oldTableName and @oldTableName <> ''
 begin
  select @columnList = substring( @columnList, 1, len(@columnList)-2 )
  select @sqlString = 'create table ' + @oldTableName + ' ('+char(13) + @columnList +')'
  select @columnList =''
      insert into #tmpTable (sql)values(@sqlString)
 end
 select @oldTableName = @tableName
 select @columnList =@columnList + @columnName + ' ' + @typeName
 if @typeName = 'varchar' or @typeName = 'char' or @typeName = 'nchar' or @typeName ='nvarchar'
  select @columnList = @columnList + '(' + rtrim(ltrim(str(@columnLength))) + ')'
 if @autoval is not null
 begin
  select @identityString = ' IDENTITY(' + ltrim(rtrim(str(IDENT_SEED( @tableName )))) + ',' +ltrim(rtrim(str(IDENT_INCR( @tableName )))) +')'
  select @columnList = @columnList + @identityString
 end
 if @isnullable = '1'
  select @columnList = @columnList + ' null'
 else
  select @columnList = @columnList + ' not null'
 select @columnList =@columnList + ',' +char(13)
    FETCH NEXT FROM myCursor into @tableName,@columnName,@columnLength,@isnullable,@typeName,@autoval
END
select @columnList = substring( @columnList, 1, len(@columnList)-1 )
select @sqlString = 'create table ' + @oldTableName + ' (' +char(13)+ @columnList +')'
insert into #tmpTable (sql)values(@sqlString)
CLOSE myCursor
DEALLOCATE myCursor
select * from #tmpTable
drop table #tmpTable
SET QUOTED_IDENTIFIER ON


----获取主键脚本

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
declare @oldTableName nvarchar(128)
declare @sqlString nvarchar(1024)
declare @columnList nvarchar(1024)
declare @constraintName nvarchar(128)
declare @oldConstraintName nvarchar(128)
declare @tableName nvarchar(1024)
declare @columnName nvarchar(1024)
declare @indexId smallint
declare @objType char(2)
declare @oldIndexId smallint
declare @oldObjType char(2)
declare @clusteredString nvarchar(16)
declare @objTypeString nvarchar(16)
select @oldIndexId = 1
select @oldObjType = 'PK'
select @oldConstraintName = ''
select @oldTableName = ''
select @sqlString = ''
select @columnList =''
select @clusteredString = ' CLUSTERED '
select @objTypeString = ' PRIMARY KEY  '
create table #tmpTable (sql nvarchar(4000))
DECLARE myCursor  CURSOR FOR
select
 i.name     as constraintName
 ,t_obj.name    as tableName
 ,col.name    as columnName
 ,i.indid    as indexId
 ,c_obj.xtype    as objType
from
 sysobjects  c_obj
 ,sysobjects  t_obj
 ,syscolumns  col
 ,master.dbo.spt_values  v
 ,sysindexes  i
where
 c_obj.xtype in ('UQ' ,'PK')
 and t_obj.id = c_obj.parent_obj
 and t_obj.xtype  = 'U'
 and t_obj.id = col.id
 and col.name = index_col(t_obj.name,i.indid,v.number)
 and t_obj.id = i.id
 and c_obj.name  = i.name
 and v.number  > 0
  and v.number  <= i.keycnt
  and v.type  = 'P'
 and t_obj.status >0
order by tablename
OPEN myCursor
FETCH NEXT FROM myCursor into @constraintName, @tableName, @columnName, @indexId, @objType
WHILE @@FETCH_STATUS = 0
BEGIN
 if @constraintName <> @oldConstraintName and @oldConstraintName <> ''
 begin
  select @columnList = substring( @columnList, 1, len(@columnList)-1 )
    if @oldIndexId > 1
   select @clusteredString = ' NONCLUSTERED '
  else
   select @clusteredString = ' CLUSTERED '
    if @oldObjType = 'UQ'
   select @objTypeString = ' UNIQUE '
  else
   select @objTypeString = ' PRIMARY KEY ' 
  select @sqlString = 'alter table ' + @oldTableName + ' WITH NOCHECK ADD CONSTRAINT ' +
   @oldConstraintName + @objTypeString + @clusteredString + ' (' + @columnList +')'
  select @columnList =''
  insert into #tmpTable (sql)values(@sqlString)
 end
  select @oldTableName = @tableName
 select @oldConstraintName = @constraintName
 select @oldIndexId = @indexId
 select @oldObjType = @objType
 select @columnList = @columnList + @columnName + ', '
    FETCH NEXT FROM myCursor into @constraintName, @tableName, @columnName, @indexId, @objType
END
select @columnList = substring( @columnList, 1, len(@columnList)-1 )
if @oldIndexId > 1
 select @clusteredString = ' NONCLUSTERED '
else
 select @clusteredString = ' CLUSTERED '
if @oldObjType = 'UQ'
 select @objTypeString = ' UNIQUE '
else
 select @objTypeString = ' PRIMARY KEY ' 
select @sqlString = 'alter table ' + @oldTableName + ' WITH NOCHECK ADD CONSTRAINT ' +
  @oldConstraintName + @objTypeString + @clusteredString + ' (' + @columnList +')'
insert into #tmpTable (sql)values(@sqlString)
CLOSE myCursor
DEALLOCATE myCursor
select * from #tmpTable
drop table #tmpTable
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON


-----获索引束脚本

declare @tableName nvarchar(128)
declare @indexName nvarchar(128)
declare @status int
declare @OrigFillFactor int
declare @columnName nvarchar(128)
declare @indid smallint
declare @clusteredString nvarchar(16)
declare @uniqueString nvarchar(16)
declare @fillfactorString nvarchar(1024)
declare @sql nvarchar(1024)
select @fillfactorString = ''
select @sql = ''
create table #tmpTable (sql nvarchar(4000))
DECLARE myCursor  CURSOR FOR
select b.name as tableName, a.name as indexName, a.status, a.OrigFillFactor, index_col(b.name, indid, 1 ) as columnName ,a.indid
from sysindexes a, sysobjects b
where a.id = b.id and b.xtype = 'U' and indid > 0 and indid < 255
and (a.status & 8388608)  = 0 --去掉不需要的记录
and (a.status & 2048) = 0 --去掉primary key
OPEN myCursor
FETCH NEXT FROM myCursor into @tableName,@indexName,@status,@OrigFillFactor,@columnName,@indid
WHILE @@FETCH_STATUS = 0
BEGIN
 if (@status & 16)<>0
  select @clusteredString = ' clustered '
 else
  select @clusteredString = ' nonclustered '
 if (@status & 2)<>0
  select @uniqueString = ' UNIQUE '
 else
  select @uniqueString = ' '
  if @OrigFillFactor <>0
  select @fillfactorString = ',FILLFACTOR=' + ltrim(rtrim(str(@OrigFillFactor)))
 else
  select @fillfactorString =''
 if (@status &1)<>0
  select @fillfactorString =  @fillfactorString + ',IGNORE_DUP_KEY'
 if (@status &256)<>0
  select @fillfactorString =  @fillfactorString + ',PAD_INDEX'
 if (@status &16777216)<>0
  select @fillfactorString =  @fillfactorString + ',STATISTICS_NORECOMPUTE'
  if  len(@fillfactorString) <> 0
  select @fillfactorString = ' with ' + substring( @fillfactorString, 2, len(@fillfactorString)-1 )
 if (@status & 4096)<>0
  select @sql = 'ALTER TABLE ' + @tableName + ' WITH NOCHECK ADD CONSTRAINT '
  + @indexName + @clusteredString + @uniqueString + '('
  + index_col(@tableName, @indid, 1 )
  + case when index_col(@tableName, @indid, 2 )is not null then ',' + index_col(@tableName, @indid, 2 ) else '' end
  + case when index_col(@tableName, @indid, 3 )is not null then ',' + index_col(@tableName, @indid, 3 ) else '' end
  + case when index_col(@tableName, @indid, 4 )is not null then ',' + index_col(@tableName, @indid, 4 ) else '' end
  + case when index_col(@tableName, @indid, 5 )is not null then ',' + index_col(@tableName, @indid, 5 ) else '' end
  + case when index_col(@tableName, @indid, 6 )is not null then ',' + index_col(@tableName, @indid, 6 ) else '' end
  + case when index_col(@tableName, @indid, 7 )is not null then ',' + index_col(@tableName, @indid, 7 ) else '' end
  + case when index_col(@tableName, @indid, 8 )is not null then ',' + index_col(@tableName, @indid, 8 ) else '' end
  + case when index_col(@tableName, @indid, 9 )is not null then ',' + index_col(@tableName, @indid, 9 ) else '' end
  + case when index_col(@tableName, @indid, 10 )is not null then ',' + index_col(@tableName, @indid, 10 ) else '' end
  + ')'
  + @fillfactorString
 else
  select @sql = 'create ' + @clusteredString + @uniqueString + ' INDEX ' + @indexName + ' ON ' + @tableName + '('
  + index_col(@tableName, @indid, 1 )
  + case when index_col(@tableName, @indid, 2 )is not null then ',' + index_col(@tableName, @indid, 2 ) else '' end
  + case when index_col(@tableName, @indid, 3 )is not null then ',' + index_col(@tableName, @indid, 3 ) else '' end
  + case when index_col(@tableName, @indid, 4 )is not null then ',' + index_col(@tableName, @indid, 4 ) else '' end
  + case when index_col(@tableName, @indid, 5 )is not null then ',' + index_col(@tableName, @indid, 5 ) else '' end
  + case when index_col(@tableName, @indid, 6 )is not null then ',' + index_col(@tableName, @indid, 6 ) else '' end
  + case when index_col(@tableName, @indid, 7 )is not null then ',' + index_col(@tableName, @indid, 7 ) else '' end
  + case when index_col(@tableName, @indid, 8 )is not null then ',' + index_col(@tableName, @indid, 8 ) else '' end
  + case when index_col(@tableName, @indid, 9 )is not null then ',' + index_col(@tableName, @indid, 9 ) else '' end
  + case when index_col(@tableName, @indid, 10 )is not null then ',' + index_col(@tableName, @indid, 10 ) else '' end
  + ')' 
  + @fillfactorString
 insert into #tmpTable (sql)values(@sql)
 FETCH NEXT FROM myCursor into @tableName,@indexName,@status,@OrigFillFactor,@columnName,@indid
end
CLOSE myCursor
DEALLOCATE myCursor
select * from #tmpTable
drop table #tmpTable


-----获取约束脚本
select 'ALTER TABLE ' + d.name + ' WITH NOCHECK ADD CONSTRAINT '  + a.name
 + case when b.status in (133141,2069) then ' default ' else ' check ' end
 + c.text 
 + case when b.status in (133141,2069) then ' for ' + col_name(b.id,b.colid) else '' end
from sysobjects a , sysconstraints b, syscomments c ,sysobjects d
where  b.constid = a.id and b.constid = c.id and b.id = d.id and d.name <> 'dtproperties'


----获取外键脚本

select distinct 
 ' alter table ' + t_obj.name +
 ' add constraint ' + c_obj.name +
 ' foreign key (' +
 col_name(t_obj.id, fkey1) +
 case when fkey2<>0 then ',' + col_name(t_obj.id, fkey2) else '' end +
 case when fkey3<>0 then ',' + col_name(t_obj.id, fkey3) else '' end +
 case when fkey4<>0 then ',' + col_name(t_obj.id, fkey4) else '' end +
 case when fkey5<>0 then ',' + col_name(t_obj.id, fkey5) else '' end +
 case when fkey6<>0 then ',' + col_name(t_obj.id, fkey6) else '' end +
 case when fkey7<>0 then ',' + col_name(t_obj.id, fkey7) else '' end +
 case when fkey8<>0 then ',' + col_name(t_obj.id, fkey8) else '' end +
 case when fkey9<>0 then ',' + col_name(t_obj.id, fkey9) else '' end +
 case when fkey10<>0 then ',' + col_name(t_obj.id, fkey10) else '' end +
 case when fkey11<>0 then ',' + col_name(t_obj.id, fkey11) else '' end +
 case when fkey12<>0 then ',' + col_name(t_obj.id, fkey12) else '' end +
 case when fkey13<>0 then ',' + col_name(t_obj.id, fkey13) else '' end +
 case when fkey14<>0 then ',' + col_name(t_obj.id, fkey14) else '' end +
 case when fkey15<>0 then ',' + col_name(t_obj.id, fkey15) else '' end +
 case when fkey16<>0 then ',' + col_name(t_obj.id, fkey16) else '' end +
  ') '  +
 ' references ' + r_obj.name + '(' +
 index_col(object_name(rkeyid), rkeyindid, 1 ) +
 case when index_col(object_name(rkeyid), rkeyindid, 2 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 2 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 3 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 3 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 4 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 4 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 5 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 5 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 6 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 6 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 7 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 7 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 8 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 8 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 9 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 9 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 10 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 10 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 11 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 11 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 12 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 12 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 13 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 13 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 14 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 14 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 15 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 15 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 16 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 16 ) else '' end +
 ')'
from
 sysobjects c_obj
 ,sysobjects t_obj
 ,sysobjects r_obj
 ,syscolumns col
 ,sysreferences  ref
where
 c_obj.xtype in ('F')
 and t_obj.id = c_obj.parent_obj
 and t_obj.id = col.id
 and col.colid   in
 (ref.fkey1,ref.fkey2,ref.fkey3,ref.fkey4,ref.fkey5,ref.fkey6,
 ref.fkey7,ref.fkey8,ref.fkey9,ref.fkey10,ref.fkey11,ref.fkey12,
 ref.fkey13,ref.fkey14,ref.fkey15,ref.fkey16)
 and c_obj.id = ref.constid
 and r_obj.id = ref.rkeyid


---获取视图脚本
select b.text as sql from sysobjects a,syscomments b
where a.xtype ='V'  and a.id = b.id and a.status >=0

---获取IF函数脚本
select b.text as sql from sysobjects a,syscomments b
where a.xtype ='IF' and a.id = b.id   and a.status >=0


---获取FN函数脚本
select b.text as sql from sysobjects a,syscomments b
where a.xtype ='FN'  and a.id = b.id   and a.status >=0

---获取过程脚本
select b.text as sql from sysobjects a,syscomments b
where a.xtype ='P' and a.id = b.id  and a.status >=0

---获取触发器脚本
select b.text as sql from sysobjects a,syscomments b
where a.xtype ='TR' and a.id = b.id and a.status >=0


---函数过程触发器解密脚本
declare @objectName varchar(100) set @objectName='????'  --要解密的对象名称
begin transaction  declare @objectname1 varchar(100)
declare @sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),
@sql4 nvarchar(4000),@sql5 nvarchar(4000),@sql6 nvarchar(4000),
@sql7 nvarchar(4000),@sql8 nvarchar(4000),@sql9 nvarchar(4000),@sql10 nvarchar(4000) 
DECLARE  @OrigSpText1 nvarchar(4000),  @OrigSpText2 nvarchar(4000) ,
 @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)
declare  @i int , @t bigint declare @m int,@n int,@q int
set @m=(SELECT max(colid) FROM syscomments  WHERE id = object_id(@objectName))
set @n=1 create table  #temp(colid int,ctext varbinary(8000))
insert #temp SELECT colid,ctext FROM syscomments  WHERE id = object_id(@objectName)
set @sql1='ALTER PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '
set @q=len(@sql1)set @sql1=@sql1+REPLICATE('-',4000-@q)
select @sql2=REPLICATE('-',4000),@sql3=REPLICATE('-',4000),
@sql4=REPLICATE('-',4000),@sql5=REPLICATE('-',4000),
@sql6=REPLICATE('-',4000),@sql7=REPLICATE('-',4000),
@sql8=REPLICATE('-',4000),@sql9=REPLICATE('-',4000),@sql10=REPLICATE('-',4000)
exec(@sql1+@sql2+@sql3+@sql4+@sql5+@sql6+@sql7+@sql8+@sql9+@sql10)
while @n<=@m begin SET @OrigSpText1=(SELECT ctext FROM #temp  WHERE colid=@n)
set @objectname1=@objectName+'_t'SET @OrigSpText3=(SELECT ctext FROM syscomments
WHERE id=object_id(@objectName) and colid=@n) if @n=1 begin
SET @OrigSpText2='CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '--
set @q=4000-len(@OrigSpText2) set @OrigSpText2=@OrigSpText2+REPLICATE('-',@q)
end else begin SET @OrigSpText2=REPLICATE('-', 4000) end SET @i=1
SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))
WHILE @i<=datalength(@OrigSpText1)/2 BEGIN
SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^
(UNICODE(substring(@OrigSpText2, @i, 1)) ^ UNICODE(substring(@OrigSpText3, @i, 1)))))
SET @i=@i+1 END SET @resultsp=REPLACE((@resultsp),'WITH ENCRYPTION', '')
SET @resultsp=REPLACE((@resultsp),'With Encryption', '')
SET @resultsp=REPLACE((@resultsp),'with encryption', '')
IF CHARINDEX('WITH ENCRYPTION',UPPER(@resultsp) )>0
SET @resultsp=REPLACE(UPPER(@resultsp),'WITH ENCRYPTION', '')
select sql=@resultsp set @n=@n+1 end drop table #temp
rollback transaction