USE STAT

GO

SET NOCOUNT ON 



IF EXISTS(SELECT 1 FROM SYS.TABLES WHERE TYPE='U' AND name='DBInfo')

	DROP TABLE DBInfo

IF EXISTS(SELECT 1 FROM SYS.TABLES WHERE TYPE='U' AND name='ColumnsInfo')

	DROP TABLE ColumnsInfo

IF EXISTS(SELECT 1 FROM SYS.TABLES WHERE TYPE='U' AND name='TableInfo')

	DROP TABLE TableInfo

IF 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 DBInfo

FROM SYS.databases D

INNER JOIN SYS.master_files MF

	ON D.database_id = MF.database_id

where D.database_id>4 

and D.name not in ('ReportServer','ReportServerTempDB','STAT')

ORDER BY D.database_id,MF.file_id



CREATE 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 @DBName

WHILE @@FETCH_STATUS=0

	BEGIN

		SET @SQL=N'

				USE '+@DBName+'

				--遍歷各庫的表信息

				;WITH CTE AS(

					SELECT id,rows 

					FROM SYS.sysindexes S

					WHERE indid<2)

				INSERT INTO STAT.DBO.TableInfo

				SELECT '''+@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 CTE

					ON CTE.ID=O.object_id

				  WHERE type=''U''  

				  order by DBName,TableName,table_modifydate desc



				--遍歷各庫的所有字段信息

				INSERT INTO STAT.DBO.ColumnsInfo

				SELECT 

					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_date

				FROM sys.columns C

					INNER JOIN sys.objects O

						ON C.[object_id]=O.[object_id]

							AND O.type=''U''

							AND O.is_ms_shipped=0

					INNER JOIN sys.types T

						ON C.user_type_id=T.user_type_id

					LEFT JOIN sys.default_constraints D

						ON C.[object_id]=D.parent_object_id

							AND C.column_id=D.parent_column_id

							AND C.default_object_id=D.[object_id]

					LEFT JOIN sys.extended_properties PFD

						ON 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 PTB

						ON 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.Name

						FROM sys.indexes IDX

						INNER JOIN sys.index_columns IDXC

							ON IDX.[object_id]=IDXC.[object_id]

								AND IDX.index_id=IDXC.index_id

						LEFT JOIN sys.key_constraints KC

							ON IDX.[object_id]=KC.[parent_object_id]

								AND IDX.index_id=KC.unique_index_id

						INNER JOIN  -- 對于一個列包含多個索引的情況,只顯示第1個索引信息

						(

							SELECT [object_id], Column_id, index_id=MIN(index_id)

							FROM sys.index_columns

							GROUP BY [object_id], Column_id

						) IDXCUQ

							ON IDXC.[object_id]=IDXCUQ.[object_id]

								AND IDXC.Column_id=IDXCUQ.Column_id

								AND IDXC.index_id=IDXCUQ.index_id

					) IDX

						ON 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.ProcInfo

				SELECT '''+@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 P

				INNER JOIN SYS.sql_modules SM

					ON P.object_id = SM.object_id

				WHERE TYPE=''P''

				'

				--PRINT @SQL

				EXEC (@SQL)

		FETCH NEXT FROM TempCursor INTO @DBName

	END

	CLOSE TempCursor

	DEALLOCATE TempCursor


  

SQL SERVER統計服務器所有的數據庫（數據庫文件）、表（表行數）、字段（各字段）等詳細信息