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

  1 --  ======================================================
  2
  3 -- 列出SQL SERVER 所有表，字段名，主键，类型，长度，小数位数等信息
  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   Syscolumns  A  Left   Join  Systypes B 
 60
 61 On   A.Xtype = B.Xusertype
 62
 63 Inner   Join  Sysobjects D 
 64
 65 On  A.Id = D.Id   And   D.Xtype = ' U '   And   D.Name <> ' Dtproperties '
 66
 67 Left   Join  Syscomments E
 68
 69 On  A.Cdefault = E.Id
 70
 71 Left   Join  Sysproperties G
 72
 73 On  A.Id = G.Id  And  A.Colid  =  G.Smallid  
 74
 75 Order   By  A.Id,A.Colorder
 76
 77 列出SQL SERVER 所有表、字段定义，类型，长度，一个值等信息
 78
 79 并导出到Excel 中
 80
 81 --  ======================================================
 82
 83 --  Export all user tables definition and one sample value
 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  ' Simplified Chinese '
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.name TableName,a.name FieldName,b.name TypeName,a.length Length,a.isnullable IS_NULL  INTO  #t
110
111 FROM   syscolumns  a,  systypes b,sysobjects d  
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, ' Sample Value           '  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=(SELECT max(len(cast( ' + @fld + '  as nvarchar))) FROM  ' + @tbl + ' ) '
150
151                -- PRINT @sql
152
153                EXEC  SP_EXECUTESQL  @sql ,N ' @maxlen int OUTPUT ' , @maxlen  OUTPUT
154
155                -- print @maxlen
156
157                SET   @sql = N ' SET @sample=(SELECT TOP 1 cast( ' + @fld + '  as nvarchar) FROM  ' + @tbl + '  WHERE len(cast( ' + @fld + '  as nvarchar))= ' + convert ( nvarchar ( 5 ), @maxlen ) + ' ) '
158
159                EXEC  SP_EXECUTESQL  @sql ,N ' @sample varchar(30) OUTPUT ' , @sample  OUTPUT
160
161                -- for quickly   
162
163                -- SET @sql=N'SET @sample=convert(varchar(20),(SELECT TOP 1 '+@fld+' FROM '+
164
165                       -- @tbl+' order by 1 desc ))'  
166
167                PRINT   @sql
168
169                print   @sample
170
171                print   @tbl
172
173                EXEC  SP_EXECUTESQL  @sql ,N ' @sample nvarchar(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   TABLE  #tc
218
219  
220
221 -- select * from ##tx
222
223  
224
225 declare   @db   nvarchar ( 60 ), @sql   nvarchar ( 3000 )
226
227 set   @db = db_name ()
228
229 -- 请修改用户名和口令 导出到Excel 中
230
231 set   @sql = ' exec master.dbo.xp_cmdshell  '' bcp ..dbo.##tx out c:/ ' + @db + ' _exp.xls -w -C936 -Usa -Psa  '''
232
233 print   @sql
234
235 exec ( @sql )
236
237 GO
238
239 DROP   TABLE  ##tx
240
241 GO
242
243  
244
245  
246
247  
248
249 --  ======================================================
250
251 -- 根据表中数据生成insert语句的存储过程
252
253 -- 建立存储过程，执行 spGenInsertSQL 表名
254
255 -- 感谢playyuer
256
257 --  ======================================================
258
259 CREATE     proc  spGenInsertSQL ( @tablename   varchar ( 256 ))
260
261  
262
263 as
264
265 begin
266
267    declare   @sql   varchar ( 8000 )
268
269    declare   @sqlValues   varchar ( 8000 )
270
271    set   @sql   = '  ( '
272
273    set   @sqlValues   =   ' values ( '' + '
274
275    select   @sqlValues   =   @sqlValues   +  cols  +   '  +  '' , ''  +  '  , @sql   =   @sql   +   ' [ '   +  name  +   ' ], '  
276
277      from  
278
279         ( select   case  
280
281                    when  xtype  in  ( 48 , 52 , 56 , 59 , 60 , 62 , 104 , 106 , 108 , 122 , 127 )                                
282
283                         then   ' case when  ' +  name  + '  is null then  '' NULL ''  else  '   +   ' cast( ' +  name  +   '  as varchar) ' + '  end '
284
285                    when  xtype  in  ( 58 , 61 )
286
287                         then   ' case when  ' +  name  + '  is null then  '' NULL ''  else  ' + '''''''''  +  '   +   ' cast( ' +  name  + '  as varchar) ' +   ' + ''''''''' + '  end '
288
289                   when  xtype  in  ( 167 )
290
291                         then   ' case when  ' +  name  + '  is null then  '' NULL ''  else  ' + '''''''''  +  '   +   ' replace( ' +  name + ' , '''''''' , '''''''''''' ) '   +   ' + ''''''''' + '  end '
292
293                    when  xtype  in  ( 231 )
294
295                         then   ' case when  ' +  name  + '  is null then  '' NULL ''  else  ' + ''' N ''''''  +  '   +   ' replace( ' +  name + ' , '''''''' , '''''''''''' ) '   +   ' + ''''''''' + '  end '
296
297                    when  xtype  in  ( 175 )
298
299                         then   ' case when  ' +  name  + '  is null then  '' NULL ''  else  ' + '''''''''  +  '   +   ' cast(replace( ' +  name + ' , '''''''' , '''''''''''' ) as Char( '   +   cast (length  as   varchar )   +   ' ))+ ''''''''' + '  end '
300
301                    when  xtype  in  ( 239 )
302
303                         then   ' case when  ' +  name  + '  is null then  '' NULL ''  else  ' + ''' N ''''''  +  '   +   ' cast(replace( ' +  name + ' , '''''''' , '''''''''''' ) as Char( '   +   cast (length  as   varchar )   +   ' ))+ ''''''''' + '  end '
304
305                    else   ''' NULL '''
306
307                  end   as  Cols,name
308
309             from  syscolumns  
310
311            where  id  =   object_id ( @tablename ) 
312
313         ) T 
314
315    set   @sql   = ' select  '' INSERT INTO [ ' +   @tablename   +   ' ] '   +   left ( @sql , len ( @sql ) - 1 ) + ' )  '   +   left ( @sqlValues , len ( @sqlValues ) - 4 )  +   ' ) ''  from  ' + @tablename
316
317    -- print @sql
318
319    exec  ( @sql )
320
321 end
322
323  
324
325 GO
326
327  
328
329  
330
331  
332
333 --  ======================================================
334
335 -- 根据表中数据生成insert语句的存储过程
336
337 -- 建立存储过程，执行 proc_insert 表名
338
339 -- 感谢Sky_blue
340
341 --  ======================================================
342
343  
344
345 CREATE   proc  proc_insert ( @tablename   varchar ( 256 ))
346
347 as
348
349 begin
350
351         set  nocount  on
352
353         declare   @sqlstr   varchar ( 4000 )
354
355         declare   @sqlstr1   varchar ( 4000 )
356
357         declare   @sqlstr2   varchar ( 4000 )
358
359         select   @sqlstr = ' select  '' insert  ' + @tablename
360
361         select   @sqlstr1 = ''
362
363         select   @sqlstr2 = '  ( '
364
365         select   @sqlstr1 =   '  values (  '' + '
366
367         select   @sqlstr1 = @sqlstr1 + col + ' + '' , '' + '  , @sqlstr2 = @sqlstr2 + name  + ' , '   from  ( select   case  
368
369 --      when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
370
371         when  a.xtype  = 104   then   ' case when  ' + a.name + '  is null then  '' NULL ''  else  ' + ' convert(varchar(1), ' + a.name  + ' ) ' + '  end '
372
373         when  a.xtype  = 175   then   ' case when  ' + a.name + '  is null then  '' NULL ''  else  ' + ''''''''' + ' + ' replace( ' + a.name + ' , '''''''' , '''''''''''' ) '   +   ' + ''''''''' + '  end '
374
375         when  a.xtype  = 61    then   ' case when  ' + a.name + '  is null then  '' NULL ''  else  ' + ''''''''' + ' + ' convert(varchar(23), ' + a.name  + ' ,121) ' +   ' + ''''''''' + '  end '
376
377         when  a.xtype  = 106   then   ' case when  ' + a.name + '  is null then  '' NULL ''  else  ' + ' convert(varchar( ' + convert ( varchar ( 4 ),a.xprec + 2 ) + ' ), ' + a.name  + ' ) ' + '  end '
378
379         when  a.xtype  = 62    then   ' case when  ' + a.name + '  is null then  '' NULL ''  else  ' + ' convert(varchar(23), ' + a.name  + ' ,2) ' + '  end '
380
381         when  a.xtype  = 56    then   ' case when  ' + a.name + '  is null then  '' NULL ''  else  ' + ' convert(varchar(11), ' + a.name  + ' ) ' + '  end '
382
383         when  a.xtype  = 60    then   ' case when  ' + a.name + '  is null then  '' NULL ''  else  ' + ' convert(varchar(22), ' + a.name  + ' ) ' + '  end '
384
385         when  a.xtype  = 239   then   ' case when  ' + a.name + '  is null then  '' NULL ''  else  ' + ''''''''' + ' + ' replace( ' + a.name + ' , '''''''' , '''''''''''' ) '   +   ' + ''''''''' + '  end '
386
387         when  a.xtype  = 108   then   ' case when  ' + a.name + '  is null then  '' NULL ''  else  ' + ' convert(varchar( ' + convert ( varchar ( 4 ),a.xprec + 2 ) + ' ), ' + a.name  + ' ) ' + '  end '
388
389         when  a.xtype  = 231   then   ' case when  ' + a.name + '  is null then  '' NULL ''  else  ' + ''''''''' + ' + ' replace( ' + a.name + ' , '''''''' , '''''''''''' ) '   +   ' + ''''''''' + '  end '
390
391         when  a.xtype  = 59    then   ' case when  ' + a.name + '  is null then  '' NULL ''  else  ' + ' convert(varchar(23), ' + a.name  + ' ,2) ' + '  end '
392
393         when  a.xtype  = 58    then   ' case when  ' + a.name + '  is null then  '' NULL ''  else  ' + ''''''''' + ' + ' convert(varchar(23), ' + a.name  + ' ,121) ' +   ' + ''''''''' + '  end '
394
395         when  a.xtype  = 52    then   ' case when  ' + a.name + '  is null then  '' NULL ''  else  ' + ' convert(varchar(12), ' + a.name  + ' ) ' + '  end '
396
397         when  a.xtype  = 122   then   ' case when  ' + a.name + '  is null then  '' NULL ''  else  ' + ' convert(varchar(22), ' + a.name  + ' ) ' + '  end '
398
399         when  a.xtype  = 48    then   ' case when  ' + a.name + '  is null then  '' NULL ''  else  ' + ' convert(varchar(6), ' + a.name  + ' ) ' + '  end '
400
401 --      when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
402
403         when  a.xtype  = 167   then   ' case when  ' + a.name + '  is null then  '' NULL ''  else  ' + ''''''''' + ' + ' replace( ' + a.name + ' , '''''''' , '''''''''''' ) '   +   ' + ''''''''' + '  end '
404
405         else   ''' NULL '''
406
407         end   as  col,a.colid,a.name
408
409         from  syscolumns a  where  a.id  =   object_id ( @tablename )  and  a.xtype  <> 189   and  a.xtype  <> 34   and  a.xtype  <> 35   and   a.xtype  <> 36
410
411        )t  order   by  colid
412
413        
414
415         select   @sqlstr = @sqlstr + left ( @sqlstr2 , len ( @sqlstr2 ) - 1 ) + ' )  ' + left ( @sqlstr1 , len ( @sqlstr1 ) - 3 ) + ' ) ''  from  ' + @tablename
416
417 --   print @sqlstr
418
419         exec (  @sqlstr )
420
421         set  nocount  off
422
423 end
424
425 GO