ITPub博客

首页 > 数据库 > 数据库开发技术 > 生成sql server2000对象创建脚本的存储过程(转)

生成sql server2000对象创建脚本的存储过程(转)

原创 数据库开发技术 作者:kitesky 时间:2005-01-10 18:17:31 0 删除 编辑

生成sql server2000对象创建脚本的存储过程
通过读取sysobjects 等系统表的纪录,生成表、视图等数据库对象脚本的存储过程。

已知问题:
存储过程长度有限制,不能超过8000个字节,因为用来存储sql脚本的变量为varchar型,最大就是8000。

-----------------------------------------------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_create_check]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_create_check]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_create_fk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_create_fk]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_create_index]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_create_index]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_create_pk_uq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_create_pk_uq]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_create_proc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_create_proc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_create_table]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_create_table]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


/*
 生成当前数据库所有CHECK约束的脚本
 适用于 Sql Server 2000
 beiqiao(qbboxcn@hotmail.com) 2004/02/29
*/
CREATE PROCEDURE sp_create_check AS
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'

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


/*
 生成当前数据库所有FK约束的脚本
 适用于 Sql Server 2000
 beiqiao(qbboxcn@hotmail.com) 2004/02/29
*/
CREATE PROCEDURE sp_create_fk AS
select
 ' 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

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


/*
 生成当前数据库所有索引的脚本
 适用于 Sql Server 2000
 beiqiao(qbboxcn@hotmail.com) 2004/02/29
*/
CREATE PROCEDURE sp_create_index AS
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
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


/*
 生成当前数据库所有PK和UQ约束的脚本
 适用于 Sql Server 2000
 beiqiao(qbboxcn@hotmail.com) 2004/02/29
*/
CREATE PROCEDURE sp_create_pk_uq AS
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 +')'
 
  --下一个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
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


/*
 生成当前数据库所有存储过程,视图,函数,触发器的脚本
 适用于 Sql Server 2000
 beiqiao(qbboxcn@hotmail.com) 2004/02/29
*/
CREATE PROCEDURE sp_create_proc AS
select b.text as sql from sysobjects a,syscomments b where a.xtype in ('TR','TF','V','P') and a.id = b.id and a.status >0

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


/*
 生成当前数据库所有表的创建脚本
 适用于 Sql Server 2000
 beiqiao(qbboxcn@hotmail.com) 2004/02/29
*/
CREATE PROCEDURE sp_create_table AS
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(1024)
declare @columnList nvarchar(1024)

declare @identityString nvarchar(128)

select @oldTableName = ''
select @columnList =''

create table #tmpTable (sql nvarchar(4000))

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 --a.status >0 是为了过滤表dtproperties

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)-1 )

  select @sqlString = 'create table ' + @oldTableName + ' (' + @columnList +')'
 
  --下一个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))) + ')'

 --添加IDENTITY限定
 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
 
 --添加null限定
 if @isnullable = '1'
  select @columnList = @columnList + ' null'
 else
  select @columnList = @columnList + ' not null'
 
 --逗号分割
 select @columnList = @columnList + ', '

    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 + ' (' + @columnList +')'
insert into #tmpTable (sql)values(@sqlString)

CLOSE myCursor
DEALLOCATE myCursor

select * from #tmpTable
drop table #tmpTable
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-----------------------------------------------------------------------------------------------------------------------------
版权声明:CSDN是本Blog托管服务提供商。如本文牵涉版权问题,CSDN不承担相关责任,请版权拥有者直接与文章作者联系解决。
发表于 2004年11月02日 5:27 PM
原文地址:http://blog.csdn.net/beiqiao/archive/2004/11/02/164028.aspx

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/66009/viewspace-786667/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-22

  • 博文量
    273
  • 访问量
    2169325