收藏几段SQL Server语句和存储过程

收藏几段SQL  Server语句和存储过程

-- ======================================================

--列出SQL SERVER 所有表，字段名，主键，类型，长度，小数位数等信息

--在查询分析器里运行即可,可以生成一个表，导出到EXCEL中

-- ======================================================

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

-------------------------------------------------------------------------------------------------

列出SQL SERVER 所有表、字段定义，类型，长度，一个值等信息

并导出到Excel 中

-- ======================================================

-- Export all user tables definition and one sample value

-- jan-13-2003,Dr.Zhang

-- ======================================================

在查询分析器里运行：

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,

[1] [2] [3]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18