转: http://blog.csdn.net/beiqiao/archive/2004/11/02/164028.aspx
通过读取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
已知问题:
存储过程长度有限制,不能超过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