博客提供了在SQL Server中列出所有表、字段名、主键、类型等信息的SQL查询语句，可在查询分析器运行生成表并导出到Excel。还给出了导出所有用户表定义和一个示例值的代码，包含游标操作、动态SQL执行等内容。
1
--======================================================
2
3--列出SQLSERVER所有表，字段名，主键，类型，长度，小数位数等信息
4
5--在查询分析器里运行即可,可以生成一个表，导出到EXCEL中
6
7--======================================================
8
9Select
10
11(CaseWhenA.Colorder=1ThenD.NameElse''End)表名,
12
13A.Colorder字段序号,
14
15A.Name字段名,
16
17(CaseWhenColumnproperty(A.Id,A.Name,'Isidentity')=1Then'√'Else''End)标识,
18
19(CaseWhen(SelectCount(*)
20
21FromSysobjects
22
23Where(NameIn
24
25(SelectName
26
27FromSysindexes
28
29Where(Id=A.Id)And(IndidIn
30
31(SelectIndid
32
33FromSysindexkeys
34
35Where(Id=A.Id)And(ColidIn
36
37(SelectColid
38
39FromSyscolumns
40
41Where(Id=A.Id)And(Name=A.Name)))))))And
42
43(Xtype='Pk'))>0Then'√'Else''End)主键,
44
45B.Name类型,
46
47A.Length占用字节数,
48
49Columnproperty(A.Id,A.Name,'Precision')As长度,
50
51Isnull(Columnproperty(A.Id,A.Name,'Scale'),0)As小数位数,
52
53(CaseWhenA.Isnullable=1Then'√'Else''End)允许空,
54
55Isnull(E.Text,'')默认值,
56
57Isnull(G.[Value],'')As字段说明
58
59FromSyscolumnsALeftJoinSystypesB
60
61OnA.Xtype=B.Xusertype
62
63InnerJoinSysobjectsD
64
65OnA.Id=D.IdAndD.Xtype='U'AndD.Name<>'Dtproperties'
66
67LeftJoinSyscommentsE
68
69OnA.Cdefault=E.Id
70
71LeftJoinSyspropertiesG
72
73OnA.Id=G.IdAndA.Colid=G.Smallid
74
75OrderByA.Id,A.Colorder
76
77列出SQLSERVER所有表、字段定义，类型，长度，一个值等信息
78
79并导出到Excel中
80
81--======================================================
82
83--Exportallusertablesdefinitionandonesamplevalue
84
85--jan-13-2003,Dr.Zhang
86
87--======================================================
88
89在查询分析器里运行：
90
91SETANSI_NULLSOFF
92
93GO
94
95SETNOCOUNTON
96
97GO
98
99
100
101SETLANGUAGE'SimplifiedChinese'
102
103go
104
105DECLARE@tblnvarchar(200),@fldnvarchar(200),@sqlnvarchar(4000),@maxlenint,@samplenvarchar(40)
106
107
108
109SELECTd.nameTableName,a.nameFieldName,b.nameTypeName,a.lengthLength,a.isnullableIS_NULLINTO#t
110
111FROMsyscolumnsa,systypesb,sysobjectsd
112
113WHEREa.xtype=b.xusertypeanda.id=d.idandd.xtype='U'
114
115
116
117DECLAREread_cursorCURSOR
118
119FORSELECTTableName,FieldNameFROM#t
120
121
122
123SELECTTOP1'_TableName'TableName,
124
125'FieldName'FieldName,'TypeName'TypeName,
126
127'Length'Length,'IS_NULL'IS_NULL,
128
129'MaxLenUsed'ASMaxLenUsed,'SampleValue'Sample,
130
131'Comment'CommentINTO#tcFROM#t
132
133
134
135OPENread_cursor
136
137
138
139FETCHNEXTFROMread_cursorINTO@tbl,@fld
140
141WHILE(@@fetch_status<>-1)---failes
142
143BEGIN
144
145IF(@@fetch_status<>-2)--Missing
146
147BEGIN
148
149SET@sql=N'SET@maxlen=(SELECTmax(len(cast('+@fld+'asnvarchar)))FROM'+@tbl+')'
150
151--PRINT@sql
152
153EXECSP_EXECUTESQL@sql,N'@maxlenintOUTPUT',@maxlenOUTPUT
154
155--print@maxlen
156
157SET@sql=N'SET@sample=(SELECTTOP1cast('+@fld+'asnvarchar)FROM'+@tbl+'WHERElen(cast('+@fld+'asnvarchar))='+convert(nvarchar(5),@maxlen)+')'
158
159EXECSP_EXECUTESQL@sql,N'@samplevarchar(30)OUTPUT',@sampleOUTPUT
160
161--forquickly
162
163--SET@sql=N'SET@sample=convert(varchar(20),(SELECTTOP1'+@fld+'FROM'+
164
165--@tbl+'orderby1desc))'
166
167PRINT@sql
168
169print@sample
170
171print@tbl
172
173EXECSP_EXECUTESQL@sql,N'@samplenvarchar(30)OUTPUT',@sampleOUTPUT
174
175INSERTINTO#tcSELECT*,ltrim(ISNULL(@maxlen,0))asMaxLenUsed,
176
177convert(nchar(20),ltrim(ISNULL(@sample,'')))asSample,''CommentFROM#twhereTableName=@tblandFieldName=@fld
178
179END
180
181FETCHNEXTFROMread_cursorINTO@tbl,@fld
182
183END
184
185
186
187CLOSEread_cursor
188
189DEALLOCATEread_cursor
190
191GO
192
193
194
195SETANSI_NULLSON
196
197GO
198
199SETNOCOUNTOFF
200
201GO
202
203selectcount(*)from#t
204
205DROPTABLE#t
206
207GO
208
209
210
211selectcount(*)-1from#tc
212
213
214
215select*into##txfrom#tcorderbytablename
216
217DROPTABL
--======================================================2
3
--列出SQLSERVER所有表，字段名，主键，类型，长度，小数位数等信息4
5
--在查询分析器里运行即可,可以生成一个表，导出到EXCEL中6
7
--======================================================8
9
Select10
11
(CaseWhenA.Colorder=1ThenD.NameElse''End)表名,12
13
A.Colorder字段序号,14
15
A.Name字段名,16
17
(CaseWhenColumnproperty(A.Id,A.Name,'Isidentity')=1Then'√'Else''End)标识,18
19
(CaseWhen(SelectCount(*)20
21
FromSysobjects22
23
Where(NameIn24
25
(SelectName26
27
FromSysindexes28
29
Where(Id=A.Id)And(IndidIn30
31
(SelectIndid32
33
FromSysindexkeys34
35
Where(Id=A.Id)And(ColidIn36
37
(SelectColid38
39
FromSyscolumns40
41
Where(Id=A.Id)And(Name=A.Name)))))))And42
43
(Xtype='Pk'))>0Then'√'Else''End)主键,44
45
B.Name类型,46
47
A.Length占用字节数,48
49
Columnproperty(A.Id,A.Name,'Precision')As长度,50
51
Isnull(Columnproperty(A.Id,A.Name,'Scale'),0)As小数位数,52
53
(CaseWhenA.Isnullable=1Then'√'Else''End)允许空,54
55
Isnull(E.Text,'')默认值,56
57
Isnull(G.[Value],'')As字段说明58
59
FromSyscolumnsALeftJoinSystypesB60
61
OnA.Xtype=B.Xusertype62
63
InnerJoinSysobjectsD64
65
OnA.Id=D.IdAndD.Xtype='U'AndD.Name<>'Dtproperties'66
67
LeftJoinSyscommentsE68
69
OnA.Cdefault=E.Id70
71
LeftJoinSyspropertiesG72
73
OnA.Id=G.IdAndA.Colid=G.Smallid74
75
OrderByA.Id,A.Colorder76
77
列出SQLSERVER所有表、字段定义，类型，长度，一个值等信息78
79
并导出到Excel中80
81
--======================================================82
83
--Exportallusertablesdefinitionandonesamplevalue84
85
--jan-13-2003,Dr.Zhang86
87
--======================================================88
89
在查询分析器里运行：90
91
SETANSI_NULLSOFF92
93
GO94
95
SETNOCOUNTON96
97
GO98
99
100
101
SETLANGUAGE'SimplifiedChinese'102
103
go104
105
DECLARE@tblnvarchar(200),@fldnvarchar(200),@sqlnvarchar(4000),@maxlenint,@samplenvarchar(40)106
107
108
109
SELECTd.nameTableName,a.nameFieldName,b.nameTypeName,a.lengthLength,a.isnullableIS_NULLINTO#t110
111
FROMsyscolumnsa,systypesb,sysobjectsd112
113
WHEREa.xtype=b.xusertypeanda.id=d.idandd.xtype='U'114
115
116
117
DECLAREread_cursorCURSOR118
119
FORSELECTTableName,FieldNameFROM#t120
121
122
123
SELECTTOP1'_TableName'TableName,124
125
'FieldName'FieldName,'TypeName'TypeName,126
127
'Length'Length,'IS_NULL'IS_NULL,128
129
'MaxLenUsed'ASMaxLenUsed,'SampleValue'Sample,130
131
'Comment'CommentINTO#tcFROM#t132
133
134
135
OPENread_cursor136
137
138
139
FETCHNEXTFROMread_cursorINTO@tbl,@fld140
141
WHILE(@@fetch_status<>-1)---failes142
143
BEGIN144
145
IF(@@fetch_status<>-2)--Missing146
147
BEGIN148
149
SET@sql=N'SET@maxlen=(SELECTmax(len(cast('+@fld+'asnvarchar)))FROM'+@tbl+')'150
151
--PRINT@sql152
153
EXECSP_EXECUTESQL@sql,N'@maxlenintOUTPUT',@maxlenOUTPUT154
155
--print@maxlen156
157
SET@sql=N'SET@sample=(SELECTTOP1cast('+@fld+'asnvarchar)FROM'+@tbl+'WHERElen(cast('+@fld+'asnvarchar))='+convert(nvarchar(5),@maxlen)+')'158
159
EXECSP_EXECUTESQL@sql,N'@samplevarchar(30)OUTPUT',@sampleOUTPUT160
161
--forquickly162
163
--SET@sql=N'SET@sample=convert(varchar(20),(SELECTTOP1'+@fld+'FROM'+164
165
--@tbl+'orderby1desc))'166
167
PRINT@sql168
169
print@sample170
171
print@tbl172
173
EXECSP_EXECUTESQL@sql,N'@samplenvarchar(30)OUTPUT',@sampleOUTPUT174
175
INSERTINTO#tcSELECT*,ltrim(ISNULL(@maxlen,0))asMaxLenUsed,176
177
convert(nchar(20),ltrim(ISNULL(@sample,'')))asSample,''CommentFROM#twhereTableName=@tblandFieldName=@fld178
179
END180
181
FETCHNEXTFROMread_cursorINTO@tbl,@fld182
183
END184
185
186
187
CLOSEread_cursor188
189
DEALLOCATEread_cursor190
191
GO192
193
194
195
SETANSI_NULLSON196
197
GO198
199
SETNOCOUNTOFF200
201
GO202
203
selectcount(*)from#t204
205
DROPTABLE#t206
207
GO208
209
210
211
selectcount(*)-1from#tc212
213
214
215
select*into##txfrom#tcorderbytablename216
217
DROPTABL
扫一扫
616
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
