SQL SERVER统计服务器所有的数据库（数据库文件）、表（表行数）、字段（各字段）等详细信息
USE STATGOSET NOCOUNT ON IF EXISTS(SELECT 1 FROM SYS.TABLES WHERE TYPE='U' AND name='DBInfo')DROP TABLE DBInfoIF EXISTS(SELECT 1 FROM SYS.TABLES WHERE TYPE='U' AND name='ColumnsInfo')DROP TABLE ColumnsInfoIF EXISTS(SELECT 1 FROM SYS.TABLES WHERE TYPE='U' AND name='TableInfo')DROP TABLE TableInfoIF EXISTS(SELECT 1 FROM SYS.TABLES WHERE TYPE='U' AND name='ProcInfo')DROP TABLE ProcInfo--数据库信息SELECT D.name AS DBName, D.database_id, D.create_date AS DBCreateDate, MF.file_id, MF.type_desc, MF.NAME AS FileName, MF.physical_name, MF.size*8/1024 AS FileSize into DBInfoFROM SYS.databases DINNER JOIN SYS.master_files MFON D.database_id = MF.database_idwhere D.database_id>4 and D.name not in ('ReportServer','ReportServerTempDB','STAT')ORDER BY D.database_id,MF.file_idCREATE TABLE [dbo].[ColumnsInfo]([DBName] [varchar](95) NULL,[TableName] [nvarchar](128) NULL,[TableDesc] [sql_variant] NULL,[Column_id] [int] NULL,[ColumnName] [sysname] NULL,[PrimaryKey] [nvarchar](1) NULL,[IDENTITY] [nvarchar](1) NULL,[Computed] [nvarchar](1) NULL,[Type] [sysname] NULL,[Length] [smallint] NULL,[Precision] [tinyint] NULL,[Scale] [tinyint] NULL,[NullAble] [nvarchar](1) NULL,[Default] [nvarchar](max) NULL,[ColumnDesc] [sql_variant] NULL,[IndexName] [sysname] NULL,[IndexSort] [varchar](4) NULL,[Create_Date] [datetime] NULL,[Modify_Date] [datetime] NULL) ON [PRIMARY] CREATE TABLE [dbo].[ProcInfo]([DBName] [varchar](83) NOT NULL,[ProcName] [sysname] NOT NULL,[object_id] [int] NOT NULL,[ProcModifyDate] [datetime] NOT NULL,[ProcCreateDate] [datetime] NOT NULL,[definition] [nvarchar](max) NULL) ON [PRIMARY]CREATE TABLE [dbo].[TableInfo]([DBName] [varchar](61) NULL,[TableName] [sysname] NULL,[object_id] [int] NULL,[table_createdate] [datetime] NULL,[table_modifydate] [datetime] NULL,[rows] [int] NULL) ON [PRIMARY]DECLARE @SQL NVARCHAR(MAX)DECLARE @DBName NVARCHAR(50)DECLARE TempCursor CURSOR FOR SELECT DISTINCT DBName FROM DBINFO OPEN TempCursor FETCH NEXT FROM TempCursor INTO @DBNameWHILE @@FETCH_STATUS=0BEGINSET @SQL=N'USE '+@DBName+'--遍历各库的表信息;WITH CTE AS(SELECT id,rows FROM SYS.sysindexes SWHERE indid<2)INSERT INTO STAT.DBO.TableInfoSELECT '''+@DBName+''' AS DBName, O.name AS TableName, O.object_id, O.create_date as table_createdate, O.modify_date as table_modifydate, CTE.rows FROM SYS.objects O INNER JOIN CTEON CTE.ID=O.object_id WHERE type=''U'' order by DBName,TableName,table_modifydate desc--遍历各库的所有字段信息INSERT INTO STAT.DBO.ColumnsInfoSELECT DBName='''+@DBName+''',TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'''' END,TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''''),Column_id=C.column_id,ColumnName=C.name,PrimaryKey=ISNULL(IDX.PrimaryKey,N''''),[IDENTITY]=CASE WHEN C.is_identity=1 THEN N''√''ELSE N'''' END,Computed=CASE WHEN C.is_computed=1 THEN N''√''ELSE N'''' END,Type=T.name,Length=C.max_length,Precision=C.precision,Scale=C.scale,NullAble=CASE WHEN C.is_nullable=1 THEN N''√''ELSE N'''' END,[Default]=ISNULL(D.definition,N''''),ColumnDesc=ISNULL(PFD.[value],N''''),IndexName=ISNULL(IDX.IndexName,N''''),IndexSort=ISNULL(IDX.Sort,N''''),Create_Date=O.Create_Date,Modify_Date=O.Modify_dateFROM sys.columns CINNER JOIN sys.objects OON C.[object_id]=O.[object_id]AND O.type=''U''AND O.is_ms_shipped=0INNER JOIN sys.types TON C.user_type_id=T.user_type_idLEFT JOIN sys.default_constraints DON C.[object_id]=D.parent_object_idAND C.column_id=D.parent_column_idAND C.default_object_id=D.[object_id]LEFT JOIN sys.extended_properties PFDON PFD.class=1 AND C.[object_id]=PFD.major_id AND C.column_id=PFD.minor_id-- AND PFD.name=''Caption'' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)LEFT JOIN sys.extended_properties PTBON PTB.class=1 AND PTB.minor_id=0 AND C.[object_id]=PTB.major_id-- AND PFD.name=''Caption'' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)LEFT JOIN -- 索引及主键信息(SELECT IDXC.[object_id],IDXC.column_id,Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,''IsDescending'')WHEN 1 THEN ''DESC'' WHEN 0 THEN ''ASC'' ELSE '''' END,PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N''√'' ELSE N'''' END,IndexName=IDX.NameFROM sys.indexes IDXINNER JOIN sys.index_columns IDXCON IDX.[object_id]=IDXC.[object_id]AND IDX.index_id=IDXC.index_idLEFT JOIN sys.key_constraints KCON IDX.[object_id]=KC.[parent_object_id]AND IDX.index_id=KC.unique_index_idINNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息(SELECT [object_id], Column_id, index_id=MIN(index_id)FROM sys.index_columnsGROUP BY [object_id], Column_id) IDXCUQON IDXC.[object_id]=IDXCUQ.[object_id]AND IDXC.Column_id=IDXCUQ.Column_idAND IDXC.index_id=IDXCUQ.index_id) IDXON C.[object_id]=IDX.[object_id]AND C.column_id=IDX.column_id-- WHERE O.name=N''要查询的表'' -- 如果只查询指定表,加上此条件ORDER BY DBName,O.name,C.column_id--遍历各库的存储过程及定义INSERT INTO STAT.dbo.ProcInfoSELECT '''+@DBName+''' AS DBName, P.name AS ProcName, P.object_id, P.modify_date AS ProcModifyDate, P.create_date AS ProcCreateDate, SM.definition FROM SYS.procedures PINNER JOIN SYS.sql_modules SMON P.object_id = SM.object_idWHERE TYPE=''P'''--PRINT @SQLEXEC (@SQL)FETCH NEXT FROM TempCursor INTO @DBNameENDCLOSE TempCursorDEALLOCATE TempCursor