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