[SQL Server] obtains the creation script of the specified table, including the table and field attributes and Foreign keys (commented out)

Use [Master]
Go
Set ansi_nulls on
Go
Set quoted_identifier on
Go
/* | <PRE>
**************************************** ************************************
Software name: May Flower ERP
Copyright: (c) 2005-2006 may flower ERP development team
Object Name: sp_helptable
Object Author: lvqy
Created on: 2005-12-04
Modification record:
Function Description: obtains the creation script of a specified table, including the table and field attributes and Foreign keys (commented out)
----------------------------------------------------------------------------
Parameter List:
1: @ tablename name of the table for which the script is to be created
**************************************** ************************************
| </PRE> */
Create procedure [DBO]. [sp_helptable] (@ tablename sysname)
As
Set nocount on
Declare @ objectid int
Declare @ tablescript table (iden int identity (1, 1), scriptline nvarchar (4000 ))
Set @ objectid = object_id (@ tablename)
If @ objectid is null or objectproperty (@ objectid, 'istable') = 0
Begin
Raiserror ('the specified object is not a table object', 16, 1)
Return
End
-- Obtain the table creation script
-- Insert Table Header
Insert into @ tablescript (scriptline)
Select n 'create table ['+ user_name (objectproperty (@ objectid, N 'ownerid') + N']. [' + object_name (@ objectid) + N'] ('

-- Insert a field
Insert into @ tablescript (scriptline)
Select n' ['+ A. Name + N'] [' + B. Name + N'] '+
Case when C. object_id is not null then n' identity ('+ convert (nvarchar, C. seed_value) + N', '+ convert (nvarchar, C. increment_value) + N ')'
Else ''end +
Case when B. xusertype in (167,175,231,239) Then n' ('+ convert (nvarchar, A. Prec) + N ')'
When B. xusertype in (106,108) Then n' ('+ convert (nvarchar,. xprec) + N', '+ convert (nvarchar,. XScale) + N ')'
Else ''end +
Case A. isnullable when 1 then n'' else n'' not 'end + n'null' +
Case when D. Name is not null then n 'default' + D. Definition else n'' end +
N ','
From SYS. syscolumns
Left join SYS. policypes B on A. xusertype = B. xusertype
Left join SYS. identity_columns C on C. object_id = A. ID and C. column_id = A. colid
Left join SYS. default_constraints D on D. parent_object_id = A. ID and D. parent_column_id = A. colid
Where a. [ID] = @ objectid
Order by A. colorder

-- Insert primary key and Index
Declare @ indexid int, @ indexscript nvarchar (4000)
Declare indexcursor cursor
Select B. index_id, n' constraint ['+ A. Name + N']' +
Case A. type when 'pk' then n'primary key' when 'uq' then n'unique' end +
Case B. type when 1 then n'clustered' when 2 then n'nonclustered' end + N '('
From SYS. key_constraints
Left join SYS. Indexes B on B. object_id = A. parent_object_id and B. index_id = A. unique_index_id
Where a. parent_object_id = @ objectid
Open indexcursor
Fetch next from indexcursor into @ indexid, @ indexscript
While @ fetch_status = 0
Begin
Select @ indexscript = @ indexscript + N' ['+ index_col (object_name (@ objectid), 2, 1) + N'],'
From SYS. index_columns
Where object_id = @ objectid
And index_id = 2

Set @ indexscript = left (@ indexscript, Len (@ indexscript)-1) + N '),'
Insert into @ tablescript (scriptline) values (@ indexscript)

Fetch next from indexcursor into @ indexid, @ indexscript
End
Close indexcursor
Deallocate indexcursor

-- Remove the last, number
Update @ tablescript
Set scriptline = left (scriptline, Len (scriptline)-1)
Where iden = (select max (iden) from @ tablescript)
Insert into @ tablescript (scriptline) values (n ')')
Insert into @ tablescript (scriptline) values (N 'Go ')
Insert into @ tablescript (scriptline) values (n '')
-- Get table remarks
Declare @ propscript nvarchar (4000)
Insert into @ tablescript (scriptline)
Select n 'exec sys. sp_addextendedproperty @ name = n''' +. name + N', @ value = n' + convert (nvarchar,. value) + n''', @ level0type = n' 'schema', @ level0name = n' +
User_name (objectproperty (. major_id, N 'ownerid') + n''', @ level1type = n''table'', @ level1name = n''' + B. name + N ''''
From SYS. extended_properties
Left join SYS. Objects B on B. [object_id] = A. major_id
Where a. major_id = @ objectid
And minor_id = 0
Insert into @ tablescript (scriptline) values ('Go ')

Declare propcursor cursor
Select n 'exec sys. sp_addextendedproperty @ name = n''' +. name + N', @ value = n' + convert (nvarchar,. value) + n''', @ level0type = n' 'schema', @ level0name = n' +
User_name (objectproperty (. major_id, N 'ownerid') + n''', @ level1type = n''table'', @ level1name = n''' + B. name + N ''' +
N', @ level2type = n' column '', @ level2name = n' + C. [name] + ''''
From SYS. extended_properties
Left join SYS. Objects B on B. [object_id] = A. major_id
Left join SYS. syscolumns C on C. [ID] = A. major_id and C. colid = A. minor_id
Where a. major_id = @ objectid
And minor_id <> 0
Open propcursor
Fetch next from propcursor into @ propscript
While @ fetch_status = 0
Begin
Insert into @ tablescript (scriptline) values (@ propscript)
Insert into @ tablescript (scriptline) values (N 'Go ')

Fetch next from propcursor into @ propscript
End
Close propcursor
Deallocate propcursor
Insert into @ tablescript (scriptline) values ('')

-- Obtain the table foreign key
Declare @ constid int, @ I tinyint, @ keycnt tinyint, @ templetsql nvarchar (400), @ sqlscript nvarchar (500 ),
@ Fcolname sysname, @ rcolname sysname,
@ Foreignline nvarchar (4000), @ referencesline nvarchar (4000), @ referencesaction nvarchar (4000)
Declare @ constidtable table (constid INT)
Select
@ Fcolname = '',
@ Rcolname = '',
@ Templetsql =
N'select @ efcolname = ''['' + col_name (fkeyid, fkey % d) + '']'', @ ercolname = ''['' + col_name (rkeyid, rkey % d) + '']'' from sys. sysreferences where constid = @ constid'

Insert into @ constidtable
Select constid from SYS. sysreferences where fkeyid = @ objectid or rkeyid = @ objectid order by fkeyid

While exists (select * From @ constidtable)
Begin
Select top 1 @ constid = constid from @ constidtable
Delete from @ constidtable where constid = @ constid

Insert into @ tablescript
Select n' -- alter table [DBO]. ['+ object_name (fkeyid) +'] With Check'
From SYS. sysreferences
Where constid = @ constid
Insert into @ tablescript (scriptline) values ('-- add' + char (13) + char (10 ))

Select @ foreignline = n' -- constraint ['+ object_name (constid) +'] foreign key (',
@ Referencesline = n' references [DBO]. ['+ object_name (rkeyid) +'] (',
@ Referencesaction = case B. delete_referential_action when 0 then n''
When 1 then n 'on Delete cascade'
When 2 then n'on Delete set null'
When 3 then n'on Delete set default'
End + ''+
Case B. delete_referential_action when 0 then n''
When 1 then n' on update cascade'
When 2 then n'on update set null'
When 3 then n'on update set default'
End,
@ Keycnt = keycnt
From SYS. sysreferences
Left join SYS. foreign_keys B on A. constid = B. object_id
Where a. constid = @ constid

-- Take the field
Set @ I = 1
While @ I <= @ keycnt
Begin
Set @ sqlscript = Replace (@ templetsql, '% d', convert (nvarchar, @ I ))
Exec sp_executesql @ stmt = @ sqlscript, @ Params = n' @ efcolname sysname output, @ ercolname sysname output, @ constid int ',
@ Efcolname = @ fcolname output, @ ercolname = @ rcolname output, @ constid = @ constid
Print @ sqlscript
Set @ foreignline = @ foreignline + case when @ I> 1 then', 'else' end + @ fcolname
Set @ referencesline = @ referencesline + case when @ I> 1 then', 'else' end + @ rcolname
Set @ I = @ I + 1
End

Insert into @ tablescript (scriptline) values (@ foreignline + N') '+ @ referencesline + N ')')
If @ referencesaction <>''
Insert into @ tablescript (scriptline) values (@ referencesaction)
Insert into @ tablescript (scriptline) values (N 'Go ')
End
-- Return the table creation script
Select scriptline from @ tablescript
Set nocount off