By reading the record of system tables such as sysObjects, the stored procedure of the database object script such as the table, the view is generated.
Known question: The length of the stored procedure is limited, and cannot exceed 8,000 bytes because the variable used to store the SQL script is VARCHAR type, the maximum is 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 goset ANSI_NULLS OFF Go
/ * Generate the current database of all CHECK constraints of the script applies to Sql Server 2000 beiqiao (qbboxcn@hotmail.com) 2004/02/29 * / CREATE PROCEDURE sp_create_check ASselect '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 '' endfrom sysobjects a, sysconstraints b, syscomments c, sysobjects dwhere b.constid = A.ID and b.constid = C.ID and B.ID = D.ID and D.Name <> ' DTProperties'
Goset quoted_identifier off goset ANSI_NULLS ON Go
Set quoted_identifier off goset ANSI_NULLS OFF Go
/ * Generate the current database All FK constraints for SQL Server 2000 Beiqiao (QBboxcn@hotmail.com) 2004/02/29 * / create procedure sp_create_fk asselect 'ALTER TABLE' T_OBJ.NAME 'Add constraint' c_obj. Name 'Foreign Key (' col_name (t_obj.id, fkey1) - Processing Compound Foreign Key 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 TEN ',' 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 TEN ',' 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 TEN ', ' col_name (t_obj.id, fkey9) Else' 'end case when fkey10 <> 0 TEN', ' 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, fk EY12) ELSE '' END CASE WHEN FKEY13 <> 0 THEN ',' col_name (t_obj.id, fkey13) else '' end case when fkey14 <> 0 TEN ',' 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 (ibject_name (rkeyid), rkeyindid, 1) - processing composite foreign key 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 dam, ' index_col (object_name (rkeyid), rkeyindid, 7) Else' 'end case when indee x_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 refwhere 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.f.f.f.f.fkey3, ref.f.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.rkeyidGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
Set quoted_identifier off goset ANSI_NULLS OFF Go
/ * Generate the current database indexes all scripts suitable for Sql Server 2000 beiqiao (qbboxcn@hotmail.com) 2004/02/29 * / CREATE PROCEDURE sp_create_index ASdeclare @tableName nvarchar (128) declare @indexName nvarchar (128) declare @status intdeclare @OrigFillFactor intdeclare @columnName nvarchar (128) declare @indid smallintdeclare @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.indidfrom sysindexes a, sysobjects bwhere a.id = B.ID and b.XTYPE = 'u' and indid> 0 and Indid <255 and (a.status & 8388608) = 0 - Remove Upon Record And (A.Status & 2048) = 0 - Remove Primary Key
Open mycursor
Fetch next from mycursor @ Tablename, @ indexname, @ status, @ OrigfillFactor, @ columnname, @ @ Indid
While @@ fetch_status = 0begin
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 dam, ' index_col (@tablename, @indid, 2) else' end case when INDEX_COL (@tablename, @indid, 3) Is Not Null Ten ',' Index_col (@tablename, @indid, 3) Else 'End Case When Index_col (@tablename, @indid, 4) Is Not Null Then', ' Index_col (@tablename, @indidID , 4) Else '' end case when INDEX_COL (@tablename, @indid, 5) is not null dam, ' 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 dam, ' index_col (@tablename, @indid, 8) else' END CASE WHEN INDEX_COL (@tablename, @indid, 9) IS not null dam, ' index_col (@tablename, @indid, 9) else' end case when index_col (@tablename, @indid, 10) IS NOT NULL TEN ',' 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 dam,' Index_col (@tablename, @indid, 3) Else '' end case when index_col (@tablename, @indid, 4) is not null dam, ' index_col (@tablename, @indid, 4) Else' end Case when INDEX_COL (@tablename, @indid, 5) is not null dam, ' 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 dam, ' index_col (@tablename, @indid, 7) Else '' end case when INDEX_COL (@tablename, @indid, 8) is not null dam, ' index_col (@tablename, @indid, 8) Else' End Case When in DEX_COL (@tablename, @indid, 9) IS not null dam, ' index_col (@tablename, @indid, 9) else' end case when INDEX_COL (@tablename, @indid, 10) is not null dam , ' index_col (@tablename, @indid, 10) Else' 'end ') ' @
fillfactorString insert into #tmpTable (sql) values (@sql) FETCH NEXT FROM myCursor into @ tableName, @ indexName, @ status, @ OrigFillFactor, @ columnName, @ indidendCLOSE myCursorDEALLOCATE myCursor
Select * from #tmptable
Drop table #tmptablegoseet quoted_identifier off goset ANSI_NULLS ON GO
Set quoted_identifier off goset ANSI_NULLS OFF Go
/ * Generate the current database of all PK and UQ constraints script applies to Sql Server 2000 beiqiao (qbboxcn@hotmail.com) 2004/02/29 * / CREATE PROCEDURE sp_create_pk_uq ASdeclare @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 smallintdeclare @objType char (2)
Declare @oldindexid SmallintDeclare @oldObjtype Char (2) Declare @clusteredString Nvarchar (16) Declare @objtypeString nvarchar (16)
select @oldIndexId = 1select @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 objTypefrom sysobjects c_obj, sysobjects t_obj, syscolumns col, master.dbo.spt_values v, Sysindexes iwhere 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 color = = 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 TablenameOpen Mycursor
Fetch next from mycursor @constraintname, @tablename, @Columnname, @Indexid, @objtype
While @@ fetch_status = 0begin
if @constraintName <> @oldConstraintName and @oldConstraintName <> '' begin - to remove the last comma 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 next start select @columnList = ''
insert into #tmpTable (sql) values (@sqlString) end select @oldTableName = @tableName select @oldConstraintName = @constraintName select @oldIndexId = @indexId select @oldObjType = @objTypeselect @columnList = @columnList @columnName ','
Fetch next from mycursor @constraintname, @tablename, @columnname, @Indexid, @objtypeend
select @columnList = substring (@columnList, 1, len (@columnList) -1) - is inserted into the last record 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 mycursordeallocate mycursor
Select * from #tmptabledrop table #tmptablegoset quoted_identifier off goset ANSI_NULLS ON Go
Set quoted_identifier off goset ANSI_NULLS OFF Go
/ * Generate all stored procedures, views, functions, and triggers for all current databases, suitable for SQL Server 2000 Beiqiao (QBboxcn@hotmail.com) 2004/02/29 * / create procedure sp_create_proc asselect 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
Goset quoted_identifier off goset ANSI_NULLS ON Go
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GO / * Create a script to generate the current database of all tables suitable for Sql Server 2000 beiqiao (qbboxcn@hotmail.com) 2004/02/29 * / CREATE PROCEDURE sp_create_table ASdeclare @count intdeclare @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 is for filtering table Dtproperties
Open mycursor
Fetch next from mycursor @ Tablename, @ ColumnName, @ ColumnLength, @ isnullable, @ Typename, @ Autoval
While @@ fetch_status = 0begin
IF @tablename <> @oldtablename and @oldtablename <> '' begin - Delete the last comma Select @columnlist = Substring (@columnlist, 1, len (@columnlist) -1)
SELECT @Sqlstring = 'Create Table' @OLDTABLENAME '(' @Columnlist ')' - Next Columnlist Start SELECT @Columnlist = ''
INSERT INTO #TMPTABLE (@sqlstring) end select @oldtablename = @tablename
select @columnList = @columnList @columnName '' @typeName - adding the data types declared if @typeName = 'varchar' or @typeName = 'char' or @typeName = 'nchar' or @typeName = 'nvarchar 'select @columnList = @columnList ' ( ' rtrim (ltrim (str (@columnLength))) ') '- adding an IDENTITY defined if @autoval is not null begin select @identityString =' IDENTITY ( ' ltrim ( RTRIM (Str (Ident_Seed (@tablename))))))) ',' LTRIM (RTR (IDENT_INCR (@tablename))))) ')' SELECT @ColumnList = @Columnlist @IDENTITITYSTRING END - Add NULL Limited IF @isnullable = '1' select @columnlist = @columnlist 'Null' else select @columnlist = @Columnlist 'not null' - comma split select @Columnlist = @columnlist ','
Fetch next from mycursor @ Tablename, @ columnname, @ columnLength, @ isnull, @ typename, @ autovaled
- inserting the last record select @columnList = substring (@columnList, 1, len (@columnList) -1) select @sqlString = 'create table' @oldTableName '(' @columnList ')' insert into # TMPTABLE (@sqlstring)
Close mycursordeallocate mycursor
Select * from #tmptabledrop table #tmptablegoset quoted_identifier off goset ANSI_NULLS ON Go
