生成sql server2000物件建立指令碼的儲存過程(轉)
生成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（） 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（） 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（） 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（） 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（） 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（） 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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/66009/viewspace-786667/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 儲存過程批量生成awr指令碼儲存過程指令碼
- 將表資料生成SQL指令碼的儲存過程和工具SQL指令碼儲存過程
- SQL 建立儲存過程PROCEDURESQL儲存過程
- SQL分隔字串的儲存過程 (轉)SQL字串儲存過程
- 建立儲存過程編譯無效物件儲存過程編譯物件
- Excel資料匯入SQL Server2000的儲存過程ExcelSQLServer儲存過程
- 自動生成對錶進行插入和更新的儲存過程的儲存過程 (轉)儲存過程
- Oracle建立儲存過程Oracle儲存過程
- MySql儲存過程—2、第一個MySql儲存過程的建立MySql儲存過程
- SQL儲存過程示例SQL儲存過程
- SQL Server 儲存過程SQLServer儲存過程
- 【SQL Server】--儲存過程SQLServer儲存過程
- 使用儲存過程(PL/SQL)向資料庫中儲存BLOB物件儲存過程SQL資料庫物件
- 從sybase的儲存過程轉向oracle的儲存過程儲存過程Oracle
- SQL Server中建立儲存過程時，with recompoile的作用SQLServer儲存過程
- SQL 儲存過程裡呼叫另一個儲存過程SQL儲存過程
- SQL Server 儲存過程的經典分頁（轉）SQLServer儲存過程
- 從系統檢視中生成儲存過程的單元測試指令碼的指令碼（供參考）儲存過程指令碼
- SqlServer儲存過程的建立與使用SQLServer儲存過程
- MySQL儲存過程的建立和使用MySql儲存過程
- SQL 分頁儲存過程SQL儲存過程
- MS SQL Server儲存過程SQLServer儲存過程
- Sql Server系列：儲存過程SQLServer儲存過程
- 解密SQL SERVER儲存過程解密SQLServer儲存過程
- SQL儲存過程迴圈SQL儲存過程
- sql儲存過程分頁SQL儲存過程
- 產生隨機密碼的sql儲存過程隨機密碼SQL儲存過程
- JAVA儲存過程(轉)Java儲存過程
- ORACLE儲存過程中建立子過程的測試！Oracle儲存過程
- 一個自動生成用ADO呼叫SQL SERVER的儲存過程VB程式碼的ADDIN (轉)SQLServer儲存過程
- laravel建立一個儲存過程Laravel儲存過程
- MySQL 建立儲存過程報錯MySql儲存過程
- sqlserver資料庫還原儲存過程指令碼SQLServer資料庫儲存過程指令碼
- 用儲存過程封裝awrrpt指令碼（四）儲存過程封裝指令碼
- 用儲存過程封裝awrrpt指令碼（三）儲存過程封裝指令碼
- 用儲存過程封裝awrrpt指令碼（二）儲存過程封裝指令碼
- 用儲存過程封裝awrrpt指令碼（一）儲存過程封裝指令碼
- 用儲存過程封裝awrrpt指令碼（五）儲存過程封裝指令碼