几则经典的SQL语句

项目已告一段落，近来比较轻松，网上闲逛，发现几则经典SQL语句，不敢独享，故贴出，以飨网友！
本人比较懒，BLOG N久都没有更新了，借此机会以更新一下，加点内容……

一、能列出SQL SERVER 中某个数据库下所有用户表，字段名，主键，类型，长度，小数位数等信息

/********************************************************************
***    列出所有用户表，字段名，主键，类型，长度，小数位数等信息
***    在查询分析器里运行即可,可以生成一个表，导出到EXCEL中
***    转载于：http://www.iaspnet.com/Article/76.aspx 具体出处不详
********************************************************************/

SELECT 

       (case when a.colorder=1 then d.name else '' end)表名,

       a.colorder 字段序号,

       a.name 字段名,

       (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,

       (case when (SELECT count(*)

       FROM sysobjects

       WHERE (name in

                 (SELECT name

                FROM sysindexes

                WHERE (id = a.id) AND (indid in

                          (SELECT indid

                         FROM sysindexkeys

                         WHERE (id = a.id) AND (colid in

                                   (SELECT colid

                                  FROM syscolumns

                                  WHERE (id = a.id) AND (name = a.name))))))) AND

              (xtype = 'PK'))>0 then '√' else '' end) 主键,

       b.name 类型,

       a.length 占用字节数,

       COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,

       isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,

       (case when a.isnullable=1 then '√'else '' end) 允许空,

       isnull(e.text,'') 默认值,

       isnull(g.[value],'') AS 字段说明    


FROM  syscolumns  a left join systypes b 

on  a.xtype=b.xusertype

inner join sysobjects d 

on a.id=d.id  and  d.xtype='U' and  d.name<>'dtproperties'

left join syscomments e

on a.cdefault=e.id

left join sysproperties g

on a.id=g.id AND a.colid = g.smallid  

order by a.id,a.colorder

二、同上，多一导出功能

/********************************************************************
***    列出所有用户表，字段名，主键，类型，长度，小数位数等信息
***    在查询分析器里运行即可,可以生成一个表，导出到EXCEL中
***    转载于：http://www.iaspnet.com/Article/76.aspx 具体出处不详
********************************************************************/

SET ANSI_NULLS OFF 

GO

SET NOCOUNT ON

GO


SET LANGUAGE 'Simplified Chinese'

go

DECLARE @tbl nvarchar(200),@fld nvarchar(200),@sql nvarchar(4000),@maxlen int,@sample nvarchar(40)


SELECT d.name TableName,a.name FieldName,b.name TypeName,a.length Length,a.isnullable IS_NULL INTO #t

FROM  syscolumns  a,  systypes b,sysobjects d  

WHERE  a.xtype=b.xusertype  and  a.id=d.id  and  d.xtype='U' 


DECLARE read_cursor CURSOR

FOR SELECT TableName,FieldName FROM #t


SELECT TOP 1 '_TableName                     ' TableName,

            'FieldName                      ' FieldName,'TypeName             ' TypeName,

            'Length' Length,'IS_NULL' IS_NULL, 

            'MaxLenUsed' AS MaxLenUsed,'Sample Value          ' Sample,

             'Comment   ' Comment INTO #tc FROM #t


OPEN read_cursor


FETCH NEXT FROM read_cursor INTO @tbl,@fld

WHILE (@@fetch_status <> -1)  --- failes

BEGIN

       IF (@@fetch_status <> -2) -- Missing

       BEGIN

              SET @sql=N'SET @maxlen=(SELECT max(len(cast('+@fld+' as nvarchar))) FROM '+@tbl+')'

              --PRINT @sql

              EXEC SP_EXECUTESQL @sql,N'@maxlen int OUTPUT',@maxlen OUTPUT

              --print @maxlen

              SET @sql=N'SET @sample=(SELECT TOP 1 cast('+@fld+' as nvarchar) FROM '+@tbl+' WHERE len(cast('+@fld+' as nvarchar))='+convert(nvarchar(5),@maxlen)+')'

              EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUT

              --for quickly   

              --SET @sql=N'SET @sample=convert(varchar(20),(SELECT TOP 1 '+@fld+' FROM '+

                     --@tbl+' order by 1 desc ))'  

              PRINT @sql

              print @sample

              print @tbl

              EXEC SP_EXECUTESQL @sql,N'@sample nvarchar(30) OUTPUT',@sample OUTPUT

              INSERT INTO #tc SELECT *,ltrim(ISNULL(@maxlen,0)) as MaxLenUsed,

                     convert(nchar(20),ltrim(ISNULL(@sample,' '))) as Sample,' ' Comment FROM #t where TableName=@tbl and FieldName=@fld

       END

       FETCH NEXT FROM read_cursor INTO @tbl,@fld

END


CLOSE read_cursor

DEALLOCATE read_cursor

GO


SET ANSI_NULLS ON

GO

SET NOCOUNT OFF

GO

select count(*)  from #t

DROP TABLE #t

GO


select count(*)-1  from #tc


select * into ##tx from #tc order by tablename

DROP TABLE #tc


--select * from ##tx


declare @db nvarchar(60),@sql nvarchar(3000)

set @db=db_name()

--请修改用户名和口令 导出到Excel 中

set @sql='exec master.dbo.xp_cmdshell ''bcp ..dbo.##tx out c:\'+@db+'_exp.xls -w -C936 -Usa -Psa '''

print @sql

exec(@sql)

GO

DROP TABLE ##tx

GO

三、将一表内容全部导出到Excel中

****************************************************************
***    數據導出EXCEL
***    導出查詢中的資料到Excel,包含欄位名,檔為真正的Excel檔,
***    如果檔不存在,將自動創建檔 ,如果表不存在,將自動創建表,
***    基於通用性考慮,僅支援導出標準資料類型
***    作者：鄒建

***    調用示例：
***     p_exporttb @sqlstr='select * from 地區資料'
***      ,@path='c:\',@fname='aa.xls',@sheetname='地區資料'
***    转载于CSDN
****************************************************************/

CREATE PROCEDURE P_ExportTB
(
@tbname sysname,        --要導出的表名
@path nvarchar(1000),   --檔存放目錄
@fname nvarchar(250)=''  --檔案名,默認為表名
)
AS
    BEGIN
        declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
        declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)

        --參數檢測
        if isnull(@fname,'') = ''  set @fname = @tbname + '.xls'

        --檢查檔是否已經存在
        if right(@path,1) <> ''  set @path = @path + ''

        create table #tb(a bit,b bit,c bit)
        set @sql = @path + @fname
        insert into #tb exec master..xp_fileexist @sql

        --資料庫創建語句
        set @sql = @path + @fname
        if exists(select 1 from #tb where a = 1)
         set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
               +';CREATE_DB="'+@sql+'";DBQ='+@sql
        else
         set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'
            +';DATABASE='+@sql+'"'
        
        --連接資料庫
        exec @err=sp_oacreate 'adodb.connection',@obj out
        if @err<>0 goto lberr
        exec @err=sp_oamethod @obj,'open',null,@constr
        if @err<>0 goto lberr
        /**//*--如果覆蓋已經存在的表,就加上下面的語句
        --創建之前先刪除表/如果存在的話
        select @sql='drop table ['+@tbname+']'
        exec @err=sp_oamethod @obj,'execute',@out out,@sql
        --*/
        --創建表的SQL
        select @sql='',@fdlist=''
        select @fdlist=@fdlist+',['+a.name+']'
         ,@sql=@sql+',['+a.name+'] '
          +case when b.name in('char','nchar','varchar','nvarchar') then
             'text('+cast(case when a.length>255 then 255 else a.length end as varchar)+')'
           when b.name in('tynyint','int','bigint','tinyint') then 'int'
           when b.name in('smalldatetime','datetime') then 'datetime'
           when b.name in('money','smallmoney') then 'money'
           else b.name end
        FROM syscolumns a left join systypes b on a.xtype=b.xusertype
        where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
         and object_id(@tbname)=id
        select @sql='create table ['+@tbname
         +']('+substring(@sql,2,8000)+')'
         ,@fdlist=substring(@fdlist,2,8000)
        exec @err=sp_oamethod @obj,'execute',@out out,@sql
        if @err<>0 goto lberr
        exec @err=sp_oadestroy @obj
        --導入數據
        set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
           ;DATABASE='+@path+@fname+''',['+@tbname+'$])'
        exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)
        return
        lberr:
         exec sp_oageterrorinfo 0,@src out,@desc out
        lbexit:
         select cast(@err as varbinary(4)) as 錯誤號
          ,@src as 錯誤源,@desc as 錯誤描述
         select @sql,@constr,@fdlist
    END

GO