server|sqlserver| Create | statement
MS SQL Server can only get the creation statements for stored procedures as follows:
Sp_helptext procedurename
But often we need to get a table creation statement, for example, when the database upgrade to determine whether a table has changed, or already have a table exists, but do not know what it is the creation of statements, the field has no constraints, there is no primary key, what indexes are created, and so on. Below I give a stored procedure for reader's reference.
The stored procedure can get the creation statements of all the tables you want, including the index associated with the table.
Code under the SQLSERVER2000
CREATE PROCEDURE Sp_get_table_info
@ObjName varchar (128)/* The table to generate SQL script * *
As
/*
* * Check to 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 IDENTITY NOT NULL,
Script Varchar (255) Not NULL,
LastLine tinyint
)
DECLARE cursor_column insensitive 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 to 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 ON
Select @Script = ' Create table ' + @ObjName + ' ('
Insert into #spscript values (@Script, 0)
/* Get column information * *
Open Cursor_column
FETCH NEXT from Cursor_column into @ColName, @ColID, @UserType, @TypeName, @Length, @Prec, @Scale,
@Status, @cDefault, @Const_Key
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)
End
End
Close Cursor_column
Deallocate Cursor_column
/* Get index information * *
Declare Cursor_index insensitive Cursor
For the Select name,indid,status from sysindexes where object_name (id) = @ObjName
and indid > 0 and indid<>255 Order by indid/* Increased the judgment of indid 255.
Open Cursor_index
Fetch Next from Cursor_index into @ColName, @IndID, @IndStatus
while (@ @FETCH_STATUS <>-1)
Begin
If @ @FETCH_STATUS <>-2
Begin
DECLARE @i TinyInt
DECLARE @thiskey varchar (50)
DECLARE @IndDesc varchar/* 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
/*
* * Check to 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 IDENTITY NOT NULL,
Script Varchar (255) Not NULL,
LastLine tinyint
)
DECLARE cursor_column insensitive Cursor
For Select A.name,a.colid,a.usertype,b.name,a.length,a.prec,a.scale,a.status, A.cdefault,
Case A.cdefault If 0 Then ' else (Select Case C.text when "(") "Then" (")" Else C.text end
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 ON
Select @Script = ' Create table ' + @ObjName + ' ('
Insert into #spscript values (@Script, 0)
/* Get column information * *
Open Cursor_column
FETCH NEXT from Cursor_column into @ColName, @ColID, @UserType, @TypeName, @Length, @Prec, @Scale,
@Status, @cDefault, @Const_Key
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)
End
End
Close Cursor_column
Deallocate Cursor_column
/* Get index information * *
Declare Cursor_index insensitive Cursor
For the Select name,indid,status,segment from sysindexes where object_name (id) = @ObjName
and indid > 0 and indid<>255 Order by indid
Open Cursor_index
Fetch Next from Cursor_index into @ColName, @IndID, @IndStatus, @Segment
while (@ @FETCH_STATUS <>-1)
Begin
If @ @FETCH_STATUS <>-2
Begin
DECLARE @i TinyInt
DECLARE @thiskey varchar (50)
DECLARE @IndDesc varchar/* 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
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
