1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
| Create Procedure P_GETTableCreateScript(@TableName Nvarchar(100))
-- 20121011 by Thomas Wassermann
-- only Structure
-- No Triggers,Foreignkeys,Indizes ...
With Execute as OWNER
as
Declare @Name NVarchar(500)
Declare @Col NVarchar(500)
Declare @Colorder int
Declare @prec int
Declare @Nullable bit
Declare @FeldTyp Nvarchar(500)
Declare @Vorgabe Nvarchar(500)
Declare @SQL Nvarchar(4000)
DECLARE Tab_Cursor CURSOR FOR
Select Distinct * from
(
Select m.Name,syscolumns.name as Col,syscolumns.colorder
,Case when syscolumns.scale is NULL and systypes.name<>'bit' then syscolumns.prec else null end as prec,syscolumns.isnullable,systypes.name as FeldTyp
,syscomments.text as Vorgabe
from syscolumns
Join sysobjects m ON m.xtype ='U' and m.Name=@TableName
join systypes on syscolumns.xtype=systypes.xtype and syscolumns.xusertype=systypes.xusertype
Left join sysobjects on sysobjects.xtype='D' and sysobjects.parent_obj=syscolumns.id
Left Join syscomments on syscomments.id=syscolumns.cdefault
where syscolumns.id=m.ID
) a
Order by colorder
OPEN Tab_Cursor
FETCH NEXT FROM Tab_Cursor
INTO @Name,@Col,@Colorder,@prec,@Nullable,@FeldTyp,@Vorgabe
Select @SQL='CREATE TABLE [' + @Name + ']' + Char(13) + Char(10) +'('
WHILE @@FETCH_STATUS = 0
BEGIN
Select @SQL = @SQL + '[' + @Col + '] [' + @FeldTyp + ']'
+ Case When @prec<>0 then ' ('+Cast(@Prec as Varchar(10))+')' else '' end
+ Case When @Nullable=1 then ' NULL' else ' NOT NULL' end
+ Coalesce(' DEFAULT '+@Vorgabe,'')
+ ','
FETCH NEXT FROM Tab_Cursor
INTO @Name,@Col,@Colorder,@prec,@Nullable,@FeldTyp,@Vorgabe
END
Select @SQL=SubString(@SQL,1,Len(@SQL)-1) + Char(13) + Char(10)+')'
CLOSE Tab_Cursor
DEALLOCATE Tab_Cursor
Select @SQL as |
Narses: Code- durch SQL-Tags ersetzt