一些有用的SQL Server语句和存储过程

1 -- ======================================================
2
3 -- 列出SQLSERVER所有表，字段名，主键，类型，长度，小数位数等信息
4
5 -- 在查询分析器里运行即可,可以生成一个表，导出到EXCEL中
6
7 -- ======================================================
8
9 Select
10
11 ( Case When A.Colorder = 1 Then D.Name Else '' End )表名,
12
13 A.Colorder字段序号,
14
15 A.Name字段名,
16
17 ( Case When Columnproperty (A.Id,A.Name, ' Isidentity ' ) = 1 Then ' √ ' Else '' End )标识,
18
19 ( Case When ( Select Count ( * )
20
21 From Sysobjects
22
23 Where (Name In
24
25 ( Select Name
26
27 From Sysindexes
28
29 Where (Id = A.Id) And (Indid In
30
31 ( Select Indid
32
33 From Sysindexkeys
34
35 Where (Id = A.Id) And (Colid In
36
37 ( Select Colid
38
39 From Syscolumns
40
41 Where (Id = A.Id) And (Name = A.Name))))))) And
42
43 (Xtype = ' Pk ' )) > 0 Then ' √ ' 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 ( Case When A.Isnullable = 1 Then ' √ ' Else '' End )允许空,
54
55 Isnull (E. Text , '' )默认值,
56
57 Isnull (G. [ Value ] , '' ) As 字段说明
58
59 From SyscolumnsA Left Join SystypesB
60
61 On A.Xtype = B.Xusertype
62
63 Inner Join SysobjectsD
64
65 On A.Id = D.Id And D.Xtype = ' U ' And D.Name <> ' Dtproperties '
66
67 Left Join SyscommentsE
68
69 On A.Cdefault = E.Id
70
71 Left Join SyspropertiesG
72
73 On A.Id = G.Id And A.Colid = G.Smallid
74
75 Order By A.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
91 SET ANSI_NULLS OFF
92
93 GO
94
95 SET NOCOUNT ON
96
97 GO
98
99
100
101 SET LANGUAGE ' SimplifiedChinese '
102
103 go
104
105 DECLARE @tbl nvarchar ( 200 ), @fld nvarchar ( 200 ), @sql nvarchar ( 4000 ), @maxlen int , @sample nvarchar ( 40 )
106
107
108
109 SELECT d.nameTableName,a.nameFieldName,b.nameTypeName,a.lengthLength,a.isnullableIS_NULL INTO #t
110
111 FROM syscolumnsa,systypesb,sysobjectsd
112
113 WHERE a.xtype = b.xusertype and a.id = d.id and d.xtype = ' U '
114
115
116
117 DECLARE read_cursor CURSOR
118
119 FOR SELECT TableName,FieldName FROM #t
120
121
122
123 SELECT TOP 1 ' _TableName ' TableName,
124
125 ' FieldName ' FieldName, ' TypeName ' TypeName,
126
127 ' Length ' Length, ' IS_NULL ' IS_NULL,
128
129 ' MaxLenUsed ' AS MaxLenUsed, ' SampleValue ' Sample,
130
131 ' Comment ' Comment INTO #tc FROM #t
132
133
134
135 OPEN read_cursor
136
137
138
139 FETCH NEXT FROM read_cursor INTO @tbl , @fld
140
141 WHILE ( @@fetch_status <> - 1 ) -- -failes
142
143 BEGIN
144
145 IF ( @@fetch_status <> - 2 ) -- Missing
146
147 BEGIN
148
149 SET @sql = N ' SET@maxlen=(SELECTmax(len(cast( ' + @fld + ' asnvarchar)))FROM ' + @tbl + ' ) '
150
151 -- PRINT@sql
152
153 EXEC SP_EXECUTESQL @sql ,N ' @maxlenintOUTPUT ' , @maxlen OUTPUT
154
155 -- print@maxlen
156
157 SET @sql = N ' SET@sample=(SELECTTOP1cast( ' + @fld + ' asnvarchar)FROM ' + @tbl + ' WHERElen(cast( ' + @fld + ' asnvarchar))= ' + convert ( nvarchar ( 5 ), @maxlen ) + ' ) '
158
159 EXEC SP_EXECUTESQL @sql ,N ' @samplevarchar(30)OUTPUT ' , @sample OUTPUT
160
161 -- forquickly
162
163 -- SET@sql=N'SET@sample=convert(varchar(20),(SELECTTOP1'+@fld+'FROM'+
164
165 -- @tbl+'orderby1desc))'
166
167 PRINT @sql
168
169 print @sample
170
171 print @tbl
172
173 EXEC SP_EXECUTESQL @sql ,N ' @samplenvarchar(30)OUTPUT ' , @sample OUTPUT
174
175 INSERT INTO #tc SELECT * , ltrim ( ISNULL ( @maxlen , 0 )) as MaxLenUsed,
176
177 convert ( nchar ( 20 ), ltrim ( ISNULL ( @sample , ' ' ))) as Sample, ' ' Comment FROM #t where TableName = @tbl and FieldName = @fld
178
179 END
180
181 FETCH NEXT FROM read_cursor INTO @tbl , @fld
182
183 END
184
185
186
187 CLOSE read_cursor
188
189 DEALLOCATE read_cursor
190
191 GO
192
193
194
195 SET ANSI_NULLS ON
196
197 GO
198
199 SET NOCOUNT OFF
200
201 GO
202
203 select count ( * ) from #t
204
205 DROP TABLE #t
206
207 GO
208
209
210
211 select count ( * ) - 1 from #tc
212
213
214
215 select * into ##tx from #tc order by tablename
216
217 DROP TABL