Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.