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
52
53
54
55
56
57
58
59
60
61
62
63
64
65 | DECLARE @collist NVARCHAR(max)
,@schema NVARCHAR(128)
,@table NVARCHAR(128);
SET @schema = N'dbo';
SET @table = N'table name';
SELECT @collist = STUFF((
SELECT N', ' + CHAR(13) + col.name + N' ' + typ.name + CASE
WHEN typ.name IN (
N'nchar'
,N'char'
,N'binary'
)
THEN '(' + cast(col.max_length AS VARCHAR) + ')'
WHEN typ.name IN (
N'nvarchar'
,N'varchar'
,N'varbinary'
)
THEN CASE
WHEN col.max_length = - 1
THEN N'(max)'
ELSE CASE
WHEN typ.name IN (
N'varchar'
,N'varbinary'
)
THEN N'(' + cast(col.max_length AS NVARCHAR) + N')'
ELSE N'(' + cast(col.max_length / 2 AS NVARCHAR) + N')'
END
END
WHEN typ.name IN (
N'numeric'
,N'decimal'
)
THEN N'(' + cast(col.precision AS NVARCHAR) + N',' + cast(col.scale AS NVARCHAR) + N')'
WHEN typ.name IN (
N'float'
,N'real'
)
THEN N'(' + cast(col.precision AS NVARCHAR) + N')'
WHEN typ.name IN (
N'datetime2'
,N'datetimeoffset'
,N'time'
)
THEN '(' + cast(col.scale AS NVARCHAR) + N')'
ELSE ''
END
FROM sys.schemas sch
INNER JOIN sys.tables tab ON (sch.schema_id = tab.schema_id)
INNER JOIN sys.columns col ON (tab.object_id = col.object_id)
INNER JOIN sys.types typ ON (
col.system_type_id = typ.system_type_id
AND col.user_type_id = typ.user_type_id
)
WHERE sch.name = @schema
AND tab.name = @table
ORDER BY col.column_id
FOR XML path('')
,TYPE
).value('.', 'nvarchar(max)'), 1, 2, '');
SELECT 'declare @tb_var Table (' + @collist + ')';
|
留言
張貼留言