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

 --  ====================================================== 
  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