MS SQLServer can only get the creation statement of the stored procedure, the method is as follows:
Sp_helptext ProcedureName
But often we need to get the creation statement of the table. For example, it is said that a table has changed when the database is upgraded, or there is already a table, but do not know what it is, there is no constraint, there is a primary key , Which indexes have been created. I will give a stored procedure for the reader's reference.
This stored procedure can get all the creative statements of all the tables you want, including the creation statement of the index associated with the table.
Code under SQLServer2000
Create Procedure SP_GET_TABLE_INFO @ ObjName Varchar (128) / * The Table To Generate SQL Script * / as
declare @Script varchar (255) declare @ColName varchar (30) declare @ColID TinyIntdeclare @UserType smallintdeclare @TypeName sysnamedeclare @Length TinyIntdeclare @Prec TinyIntdeclare @Scale TinyIntdeclare @Status TinyIntdeclare @cDefault intdeclare @DefaultID TinyIntdeclare @Const_Key varchar (255) declare @ Indid Smallint Declare @indstatus INTDECLARE @index_key varchar (255) Declare @dbname varchar (30) Declare @strpri_key varchar (255)
/ *** Check to see the the Table exists and initialize @objid. * / If not exists (select name from sysobjects where name = @objname) Begin select @dbname = db_name () Raiserror (15009, -1, -1, @ Objname, @ dbname) Return (1) End
Create Table #SPScript (ID Int IDENTIN NOT NULL, SCRIPT VARCHAR (255) Not Null, Lastline Tinyint
Declare Cursor_Column Innsitive Cursor for Select A.Name, A.Colid, A.USERTYPE, B.NAME, A.LENGTH, A.PREC, A.SCALE, A.STATUS, A. CDEFAULT, CASE A. CDEFAULT WHEN 0 THEN ' 'else (select c.Text from syscomments c where a.cdefault = c.id) end const_key from syscolumns a, systypes b where object_name (a.id) = @ObjName and a.usertype = b.usertype order by a.ColID
SET NOCOUNT ONSELECT @Script = 'Create Table' @objname '(' INSERT INTO #SPScript Values (@ script, 0) / * Get Column Information * / Open Cursor_COLUMN
Fetch next from cursor_column @ colname, @ colid, @ @ @ @ PREC, @ scale, @ status, @ cdefault, @ const_key
SELECT @script = '' while (@@ fetch_status <> -1) begin if (@@ fetch_status <> -2) begin select @script = @colname '' @Typename if @USERTYPE IN (1, 2, 3 , 4) SELECT @Script = @script '(' Convert (Char (3), @ Length) ')' Else if @USERTYPE IN (24) SELECT @Script = @Script '(' Convert (charr (3), @ PREC) ',' Convert (CHAR (3), @ Scale) ')' Else SELECT @Script = @Script '' IF (@Status & 0x80)> 0 Select @script = @ Script 'Identity (1, 1)'
if (@Status & 0x08)> 0 Select @Script = @Script 'NULL' else Select @Script = @Script 'NOT NULL' if @cDefault> 0 Select @Script = @Script 'DEFAULT' @Const_Key end fetch next from Cursor_Column into @ ColName, @ ColID, @ UserType, @ TypeName, @ Length, @ Prec, @ Scale, @ Status, @ cDefault, @ Const_Key if @@ FETCH_STATUS = 0 begin Select @Script = @Script ', 'Insert into #spscript values (@ Script, 0) end else begin Insert into #spscript values (@ Script, 1) Insert into #spscript values (') ', 0) endendClose Cursor_ColumnDeallocate Cursor_Column / * Get index information * / Declare Cursor_Index INSENSITIVE CURSOR for Select name, indID, status from sysindexes where object_name (id) = @ ObjName and indID> 0 and indID <> 255 order by indID / * Added inDid 255 determines * / Open Cursor_IndexFetch Next from Cursor_Index into @ Colname, @indid, @INDSTATUSWHILE (@@ fetch_status <> -1) begin if @@ fetch_status <> -2 begin
Declare @i tinyint declare @thiskey varchar (50) Declare @inddesc varchar (68) / * String to build up index desc in * /
Select @i = 1 while (@i <= 16) begin select @thiskey = index_col (@objname, @indid, @i) if @Thiskey Is Null Break
IF @i = 1 Select @index_key = index_col (@objname, @indid, @i) else select @index_key = @index_key ',' index_col (@objname, @indid, @i) select @i = @i 1 end if (@indstatus & 0x02)> 0 Select @script = 'create unique' else select @script = 'create' if @indid = 1 select @script = @script 'clustered'
if (@IndStatus & 0x800)> 0 select @strPri_Key = 'PRIMARY KEY (' @Index_Key ')' else select @strPri_Key = '' if @IndID> 1 select @Script = @Script 'nonclustered' Select @Script = @Script 'index' @coLname 'on' @objname '(' @index_key ')' select @inddesc = '' / * ** See if the index is ignore_dupkey (0x01). * / IF @indstatus & 0x01 = 0x01 select @inddDesc = @inddesc 'ignore_dup_key' ',' / * ** See if the index is ignore_dup_row (0x04). * / / * if @indstatus & 0x04 = 0x04 * / / * SELECT @inddesc = @inddesc 'ignore_dup_row' ',' * / / * 2000 is not supported * / / * ** see if the index is allow_dup_row (0x40). * / If @indstatus & 0x40 = 0x40 select @indDesc = @Inddesc 'allow_dup_row' ',' IF @indDesc <> '' begin select @inddesc = Substring (@inddesc, 1, datalength (@inddesc) - 1) SELECT @Script = @Script 'with' @inddesc End / * ** Add The location of the data. * / End if (@strPri_Key = '') Insert into #spscript values (@ Script, 0) else update #spscript set Script = Script @strPri_Key where LastLine = 1 Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatusendClose Cursor_indexdeallocate Cursor_indexselect Script from #SPScript
Set nonent offreturn (0)
Code under SQLServer6.5
Create Procedure SP_GET_TABLE_INFO @ ObjName Varchar (128) / * The Table To Generate SQL Script * / as
declare @Script varchar (255) declare @ColName varchar (30) declare @ColID TinyIntdeclare @UserType smallintdeclare @TypeName sysnamedeclare @Length TinyIntdeclare @Prec TinyIntdeclare @Scale TinyIntdeclare @Status TinyIntdeclare @cDefault intdeclare @DefaultID TinyIntdeclare @Const_Key varchar (255) declare @ Indid SmallintDeclare @indstatus smallintddeclare @index_key varchar (255) Declare @segment smallintdeclare @dbname varchar (30) Declare @strpri_key varchar (255)
/ *** Check to see the the Table exists and initialize @objid. * / If not exists (select name from sysobjects where name = @objname) Begin select @dbname = db_name () Raiserror (15009, -1, -1, @ Objname, @ dbname) Return (1) End
Create Table #SPScript (ID Int IDENTIN NOT NULL, SCRIPT VARCHAR (255) Not Null, Lastline Tinyint
Declare Cursor_Column Innsitive Cursor for Select A.Name, A.Colid, A.USERTYPE, B.NAME, A.LENGTH, A.PREC, A.SCALE, A.STATUS, A. CDEFAULT, CASE A. CDEFAULT WHEN 0 THEN ' 'Else (select case c.text when "" "" "" Else C.Text end from syscomments c where a.cdefault = c.id) End const_key from syscolumns a, systempes b where object_name A.ID) = @objname and a.USERTYPE = B.USERTYPE ORDER BY A.COLID
Set NoCount onselect @script = 'create table' @objname '(' Insert Into #spscript Values (@ script, 0)
/ * Get column information * / Open Cursor_Column
Fetch next from cursor_column @ colname, @ colid, @ @ @ @ PREC, @ scale, @ status, @ cdefault, @ const_key
SELECT @script = '' while (@@ fetch_status <> -1) begin if (@@ fetch_status <> -2) begin select @script = @colname '' @Typename if @USERTYPE IN (1, 2, 3 , 4) SELECT @Script = @script '(' Convert (Char (3), @ Length) ')' Else if @USERTYPE IN (24) SELECT @Script = @Script '(' Convert (charr (3), @ PREC) ',' Convert (CHAR (3), @ Scale) ')' Else SELECT @Script = @Script '' IF (@Status & 0x80)> 0 Select @script = @ Script 'Identity (1, 1)'
if (@Status & 0x08)> 0 Select @Script = @Script 'NULL' else Select @Script = @Script 'NOT NULL' if @cDefault> 0 Select @Script = @Script 'DEFAULT' @Const_Key end fetch next from Cursor_Column into @ ColName, @ ColID, @ UserType, @ TypeName, @ Length, @ Prec, @ Scale, @ Status, @ cDefault, @ Const_Key if @@ FETCH_STATUS = 0 begin Select @Script = @Script ', 'Insert into #spscript values (@ Script, 0) end else begin Insert into #spscript values (@ Script, 1) Insert into #spscript values (') ', 0) endendClose Cursor_ColumnDeallocate Cursor_Column
/ * Get index information * / Declare Cursor_Index INSENSITIVE CURSOR for Select name, IndID, status, Segment from sysindexes where object_name (id) = @ ObjName and IndID> 0 and IndID <> 255 order by IndIDOpen Cursor_IndexFetch Next from Cursor_Index into @ColName, @IndID, @IndStatus, @Segmentwhile (@@ FETCH_STATUS <> -1) begin if @@ FETCH_STATUS <> -2 begindeclare @i TinyInt declare @thiskey varchar (50) declare @IndDesc varchar (68) / * string to build up INDEX DESC in * /
Select @i = 1 while (@i <= 16) begin select @thiskey = index_col (@objname, @indid, @i) if @Thiskey Is Null Break
IF @i = 1 Select @index_key = index_col (@objname, @indid, @i) else select @index_key = @index_key ',' index_col (@objname, @indid, @i) select @i = @i 1 end if (@indstatus & 0x02)> 0 Select @script = 'create unique' else select @script = 'create' if @indid = 1 select @script = @script 'clustered'
if (@IndStatus & 0x800)> 0 select @strPri_Key = 'PRIMARY KEY (' @Index_Key ')' else select @strPri_Key = '' if @IndID> 1 select @Script = @Script 'nonclustered' Select @Script = @Script 'index' @coLname 'on' @objname '(' @index_key ')' select @inddesc = '' / * ** See if the index is ignore_dupkey (0x01). * / if @IndStatus & 0x01 = 0x01 Select @IndDesc = @IndDesc 'IGNORE_DUP_KEY' ',' / * ** See if the index is ignore_dup_row (0x04). * / if @IndStatus & 0x04 = 0x04 Select @IndDesc = @IndDesc 'IGNORE_DUP_ROW' ',' / * ** See if the index is allow_dup_row (0x40). * / if @IndStatus & 0x40 = 0x40 Select @IndDesc = @IndDesc 'aLLOW_DUP_ROW' ',' if @IndDesc <> '' Begin Select @inddesc = Substring (@inddesc, 1, datalength (@inddesc) - 1) SELECT @Script = @Script 'with' @indDesc END / * ** Add the location of the data. * / If @Segment <> 1 select @Script = @Script 'ON' name from syssegments where segment = @Segment end if (@strPri_Key = '') Insert into #spscript values (@ Script, 0) else update #spscript set Script = Script @strPri_Key WHERE Lastline =
1 fetch next from cursoor_index @colname, @indid, @indstatus, @segmentendclose cursor_indexdeallocate cursor_indexselect script from #SPScript Order By ID
Set nocount off
Return (0)
