Automatic generate create table sql
Click (here) to collapse or open
-- CREATE PROCEDURE gen_createtbl @ tbl_name varchar
-- AS
Set nocount on
-- declare the variables required by the cursor
Declare @ tblname varchar
@ colno int
@ colname varchar
@ IsIdt bit
@ ispk bit
@ type varchar
@ length int
@ decim int
@ isnull bit
@ default varchar
@ sql varchar (2000),-- for create table
@ sql2 varchar (1000)-- for create Competition
@ sql3 varchar (1000),-- for create CONSTRAINT
@ sql4 varchar (1000),-- for default value
@ sql5 varchar (1000),-- for col comments
@ sql6 varchar (1000),-- for table comments
@ tbl varchar
@ idx varchar
@ idxp varchar
@ colname2 varchar
@ comments varchar,-- comment
@ tbcomments varchar
The arrangement position of this field in the @ col_id int,-- index
Total number of columns contained in the @ col_num int,-- index
@ idx_type_desc varchar,-- Index type description
@ is_unique bit-is it the only one
Set @ tblname='sbj_retail_store_info'
Set @ sql4=''
Declare that the number of parameters in a cursor mycursor,select statement must be the same as the variable name taken from the cursor
Declare mycursor cursor for
SELECT table name = case when a.colorder=1 then d.name else''end
Table description = cast ((case when a.colorder=1 then isnull (f. Valuememe') else 'end) as varchar
Field serial number = a.colorder
Field name = a.name
Logo = case when COLUMNPROPERTY (a. ID _ 1'else. A. Name _ then _
Primary key = case when exists (SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in (SELECT indid FROM sysindexkeys WHERE id= a.id AND colid=a.colid)) then 1 else 0 end
Type = b.name
-- number of bytes occupied = a.length
Length = COLUMNPROPERTY (a. ID. A. Name.
Number of decimal places = isnull (COLUMNPROPERTY (a. ID.
Allow null = case when a.isnullable=1 then '1'else' 'end
Default value = isnull (e.textquarter')
Field description = cast (isnull (g. [value],') as varchar (100)
FROM syscolumns a
Left join systypes b on a.xusertype=b.xusertype
Inner join sysobjects d on a.id=d.id and d.xtypewritten properties U' and d.nameplates dtproperties`
Left join syscomments e on a.cdefault=e.id
Left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id and g. Nameplate
Left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 and f. Nameplate
Where d.name = @ tblname-- if only the specified table is queried, plus this condition, the table name
Order by a.id,a.colorder
/ * create temp table to get the comments*/
Create table # comtmp (
[sql] varchar (3000)
)
/ * create temp table to get index info and order*/
Create table # idxtmp (
[tb_name] varchar
[idx_name] varchar
[col_name] varchar
[col_id] int
[idx_type_desc] varchar
[is_unique] bit)
Insert into # idxtmp
SELECT
Tab.name AS [tb_name],-- [table name]
Idx.name AS [idx_name],-- [constraint name]
Col.name AS [col_name],-- [constraint column name]
IdxCol.key_ordinal AS [col_id],-- [Index column order]
Idx.type_desc as [IDX _ type_desc],-- [index type description]
Idx.is_unique AS [is_unique]-- [is it unique]
FROM
Sys.indexes idx
JOIN sys.index_columns idxCol
ON (idx.object_id = idxCol.object_id
AND idx.index_id = idxCol.index_id
AND idx.is_unique_constraint = 1)
JOIN sys.tables tab
ON (idx.object_id = tab.object_id)
JOIN sys.columns col
ON (idx.object_id = col.object_id
AND idxCol.column_id = col.column_id)
Where tab.name=@tblname
Declare mycursor2 cursor for
Select a.[tb _ name], a.[idx _ name], a.[col _ name], a.[col _ id], b.[col _ num], a.[idx _ type_desc], a.[is _ unique]
From # idxtmp a
Left join (select [tb_name], [idx_name], count (1) col_num from # idxtmp group by [tb_name], [idx_name]) b
On a.tb_name=b.tb_name
And a.idxroomnameb = b.[ IDX _ name]
-- Open the cursor
Open mycursor
Take the data from the cursor and assign it to the two variables we just declared
Fetch next from mycursor into @ tblname,@tbcomments,@colno,@colname,@IsIdt,@ispk,@type,@length,@decim,@isnull,@default,@comments
-- determine the status of the cursor
-- 0 fetch statement succeeded
-1 the fetch statement failed or the row is not in the result set
-2 the extracted row does not exist
While (@ @ fetch_status=0)
Begin
-- shows the value we take out with a cursor each time
-- print 'cursor successfully fetched a piece of data'
If @ colno=1
Begin
Set @ tbl=@tblname
Set @ sql='CREATE TABLE [dbo]. ['+ @ tblname+'] (
['+ @ colname+'] ['+ @ type+']'+ (case @ isnull when 0 then 'NOT NULL,' else' NULL,'end)
Set @ sql6='EXEC sys.sp_addextendedproperty @ name=N'+''''+'MS_Description'+''',@value=N'+''''+@tbcomments+''',@level0type=N'+'''SCHEMA'+''',@level0name=N'+'''dbo'
+'', @ level1type=N'+'''TABLE'+''',@level1name=N'+''''+@tbl+''''
Insert into # comtmp ([sql]) values (@ sql6)
-- print @ sql6
Set @ sql5='EXEC sys.sp_addextendedproperty @ name=N'+'''MS_Description'+''', @ value=N'+''''+@comments+''',@level0type=N'+''''+'SCHEMA'+''',@level0name=N'
+''dbo'+''',@level1type=N'+''''+'TABLE'+''''+',@level1name=N'+''''+@tbl+''', @ level2type=N'+''''+'COLUMN'+''','+'@level2name=N'+''''+@colname+''''
Insert into # comtmp ([sql]) values (@ sql5)
-- print @ sql5
End
Else
Begin
Set @ tbl=@tbl+''
-- remove the five fields of ETL_CRC QA_RULE_CHK_FLG QA_MANUAL_FLG CREATE_BY UPDATE_BY
If @ colname in ('ETL_CRC','QA_RULE_CHK_FLG','QA_MANUAL_FLG','CREATE_BY','UPDATE_BY')
Begin
Set @ sql=@sql+''
End
Else
Begin
Set @ sql=@sql+'
'+' ['+ @ colname+'] ['+ (case @ type)
When 'timestamp' then' bigint'+']'
When 'varchar' then @ type +']'+'('+ cast (@ length as varchar (10)) +')'
When 'nvarchar' then @ type +']'+'('+ cast (@ length as varchar (10)) +')'
When 'char' then @ type +']'+'('+ cast (@ length as varchar (10)) +')'
When 'decimal' then @ type +']'+ ('+ cast (@ length as varchar (10)) +','+ cast (@ decim as varchar (3)) +')'
Else @ type+'] 'end) +
(case @ isnull when 0 then 'NOT NULL,' else' NULL,'end)
Set @ sql5='EXEC sys.sp_addextendedproperty @ name=N'+'''MS_Description'+''', @ value=N'+''''+@comments+''',@level0type=N'+''''+'SCHEMA'+''',@level0name=N'
+''dbo'+''',@level1type=N'+''''+'TABLE'+''''+',@level1name=N'+''''+@tbl+''', @ level2type=N'+''''+'COLUMN'+''','+'@level2name=N'+''''+@colname+''''
-- print @ sql5
Insert into # comtmp ([sql]) values (@ sql5)
End
End
If @ ispk=1
Begin
Set @ sql2='PRIMARY KEY CLUSTERED
(
['+ @ colname+'] ASC
))
GO'
End
Else
Begin
Set @ sql2=@sql2+''
End
If @ default''and @ colname not in (' ETL_CRC','QA_RULE_CHK_FLG','QA_MANUAL_FLG','CREATE_BY','UPDATE_BY')
Begin
Set @ sql4=@sql4+'
ALTER TABLE [dbo]. ['+ @ tbl+'] ADD DEFAULT'+ @ default+' FOR ['+ @ colname+']
GO'
End
Else
Begin
Set @ sql4=@sql4+''
End
-- use a cursor to fetch the next record
Fetch next from mycursor into @ tblname,@tbcomments,@colno,@colname,@IsIdt,@ispk,@type,@length,@decim,@isnull,@default,@comments
End
-- close the cursor
Close mycursor
-- undo the cursor
DEALLOCATE mycursor
Print @ sql
Print @ sql2
Print @ sql4
Declare that the number of parameters in a cursor mycursor,select statement must be the same as the variable name taken from the cursor
-- Open the cursor
Set @ idx=0
Open mycursor2
Take the data from the cursor and assign it to the two variables we just declared
Fetch next from mycursor2 into @ tblname,@idx,@colname2,@col_id,@col_num,@idx_type_desc,@is_unique
-- determine the status of the cursor
-- 0 fetch statement succeeded
-1 the fetch statement failed or the row is not in the result set
-2 the extracted row does not exist
While (@ @ fetch_status=0)
Begin
-- shows the value we take out with a cursor each time
If @ idxisnull (@ idxp,'')
Begin
Set @ sql3='ALTER TABLE [dbo]. ['+ @ tblname+'] ADD CONSTRAINT ['+ @ idx+']'+ (case when @ is_unique=1 then'UNIQUE 'else' 'end) + @ idx_type_desc+'
(['+ @ colname2+'] ASC'
End
Else
Begin
Set @ sql3=@sql3+'
['+ @ colname2+'] ASC'
End
If @ col_id
