[SQL] 以SQL Command查詢DB內Table和Column資料

2016-04-10

1277
0
SQL
2016-04-28

很久以前無聊寫的東西, 以SQL Command查詢DB中的table有哪些, 儲存到Table中,
查詢以多少個Table, 和資料筆數, 做成StoreProcedure, 再定期將資料儲存起來.
可以將這些結果查詢Table中的資料成長數量.
(使用在MS SQL Server 2008-R2, 其它DB的語法會有些與不同唷)

如前言.
不多說, 直接上Code吧.

第一段, 建立 DATABASE [Administration]
和 DB_Control, DB_Record, DB_FileUseSize
三個Table的用途等等就會知道囉,

(這邊做了一個錯誤示範, 我懶得改了, Column建議不要用關鍵字命名.
那時候居然用 Roe取Column的名字 XDDD)

USE [master]
GO

/****** Object:  Database [Administration]    Script Date: 03/28/2015 00:53:52 ******/
CREATE DATABASE [Administration] ON  PRIMARY 
( NAME = N'Administration', FILENAME = N'C:\Program Files\Microsoft SQL Server\MyAdminTable\Administration.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Administration_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MyAdminTable\Administration_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [Administration] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Administration].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [Administration] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [Administration] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [Administration] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [Administration] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [Administration] SET ARITHABORT OFF 
GO

ALTER DATABASE [Administration] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [Administration] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [Administration] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [Administration] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [Administration] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [Administration] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [Administration] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [Administration] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [Administration] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [Administration] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [Administration] SET  DISABLE_BROKER 
GO

ALTER DATABASE [Administration] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [Administration] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [Administration] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [Administration] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [Administration] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [Administration] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [Administration] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [Administration] SET  READ_WRITE 
GO

ALTER DATABASE [Administration] SET RECOVERY FULL 
GO

ALTER DATABASE [Administration] SET  MULTI_USER 
GO

ALTER DATABASE [Administration] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [Administration] SET DB_CHAINING OFF 
GO


USE [Administration]
GO

/****** Object:  Table [dbo].[DB_Control]    Script Date: 03/28/2015 00:54:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DB_Control](
	[DbSerID] [int] IDENTITY(1,1) NOT NULL,
	[ServerName] [varchar](255) NOT NULL,
	[DbName] [varchar](255) NOT NULL,
 CONSTRAINT [PK_DB_Control] PRIMARY KEY CLUSTERED 
(
	[DbSerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


USE [Administration]
GO

/****** Object:  Table [dbo].[DB_Record]    Script Date: 03/28/2015 00:54:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DB_Record](
	[RecordID] [int] IDENTITY(1,1) NOT NULL,
	[DbSerID] [int] NOT NULL,
	[TableName] [nvarchar](255) NOT NULL,
	[Row] [int] NOT NULL,
	[TableSize] [int] NOT NULL,
	[Reserved] [int] NOT NULL,
	[Index_Size] [int] NOT NULL,
	[Unused] [int] NOT NULL,
	[RecordTime] [datetime] NOT NULL,
 CONSTRAINT [PK_DB_Record_1] PRIMARY KEY CLUSTERED 
(
	[RecordID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


USE [Administration]
GO

/****** Object:  Table [dbo].[DB_FileUseSize]    Script Date: 03/28/2015 00:54:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DB_FileUseSize](
	[DbSerId] [int] IDENTITY(1,1) NOT NULL,
	[DBName] [nvarchar](255) NOT NULL,
	[DBFileName] [nvarchar](255) NOT NULL,
	[FileSize] [int] NOT NULL,
	[UseSize] [int] NOT NULL,
	[RecordTime] [datetime] NOT NULL,
 CONSTRAINT [PK_DbSerId] PRIMARY KEY CLUSTERED 
(
	[DbSerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


USE [Administration]
GO

/****** Object:  Table [dbo].[DB_LogFile]    Script Date: 03/28/2015 00:54:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DB_LogFile](
	[DBSerId] [int] IDENTITY(1,1) NOT NULL,
	[DBName] [nvarchar](255) NOT NULL,
	[DBLogSize(Mb)] [nvarchar](255) NOT NULL,
	[LogSpaceUsed(%)] [nvarchar](255) NOT NULL,
	[RecordTime] [datetime] NOT NULL,
 CONSTRAINT [PK_DB_LogFile] PRIMARY KEY CLUSTERED 
(
	[DBSerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

第二段取得各DB 名稱, 和檔案的大小, 寫入剛剛建立的Table [DB_FileUseSize] 中.

IF OBJECT_ID('tempdb..#depTemp') IS NOT NULL 
    DROP TABLE #depTemp 
      
--DELETE from [Administration].[dbo].[DB_Control]
--以指定的sql語句,建立臨時表 
SELECT NAME,ROW_NUMBER()  
       OVER(ORDER BY NAME) AS rowNumber INTO #depTemp 
FROM   MASTER..SysDatabases    
    
DECLARE @max INT --最大行數   
SELECT @max = MAX(rowNumber) FROM   #depTemp 
    
DECLARE @rowNo INT --行號   
SET @rowNo = 1  
 
DECLARE @table INT --Table是否存在   
--SET @table = 0  
 
DECLARE @dbNameTemp VARCHAR(255) 
--用來對每一個rowNumber來進行迴圈操作 
WHILE @rowNo <= @max  
    BEGIN 
        --此處對每一行要進行的操作的代碼   
        SELECT @dbNameTemp=NAME FROM #depTemp WHERE rowNumber=@rowNo 
                --檢查是否有該筆db的紀錄 如果沒有就新增進去
                select @table=cast(count(DbSerID) as int) from [Administration].[dbo].[DB_Control] where DbName=@dbNameTemp
                        if @table  =0 --then
                                INSERT INTO [Administration].[dbo].[DB_Control] ([ServerName],[DbName])       VALUES (@@servername ,cast(@dbNameTemp as varchar(255)))
                        --end if
        SET @rowNo = @rowNo + 1 
    END 
DROP TABLE #depTemp--清除臨時表
 
 
IF OBJECT_ID('tempdb..#depTemp1') IS NOT NULL 
    DROP TABLE #depTemp1 
 
select DbSerId,dbname into #depTemp1
from [Administration].[dbo].[DB_Control] where ServerName=@@servername
--and dbname <> 'Test'
and dbname <> 'tempdb'
and DbName not like '%[%'
and DbName not like '%]%'
and DbName not like '%(%'
and DbName not like '%)%'
and DbName not like '%{%'
and DbName not like '%}%'
and DbName not like '%@%'
and DbName not like '%$%'
DECLARE @DbSerId INT
DECLARE @dbname nvarchar(255)
--DECLARE @max INT --最大行數   
SELECT @max = MAX(DbSerId) FROM [Administration].[dbo].[DB_Control] where ServerName=@@servername
DECLARE @min INT --最大行數   
SELECT @min = Min(DbSerId) FROM [Administration].[dbo].[DB_Control] where ServerName=@@servername
--DECLARE @rowNo INT --行號   
SET @rowNo = @min
Declare @vSQL varchar(max)
 
WHILE @rowNo <= @max  
    BEGIN 
        --此處對每一行要進行的操作的代碼   
                select @DbSerId=DbSerId,@dbname=dbname from #depTemp1 WHERE DbSerId=@rowNo
                --取得DB為@dbname
                --DB編號  @DbSerId
                SET @vSQL = N'
                                        USE ['+@dbname +' ]
                                        IF OBJECT_ID(''tempdb..#tmpDB'') IS NOT NULL
                                        DROP TABLE #tmpDB
                                        CREATE TABLE #tmpDB
                                        (DBName SYSNAME DEFAULT(DB_NAME()), Fileid INT, FileGroup INT, TotalExtents INT, UsedExtents INT, Name SYSNAME, FileName NVARCHAR(4000))   
                                       
                                        INSERT #tmpDB(Fileid,FileGroup,TotalExtents,UsedExtents,Name,FileName)
                                        EXEC (''DBCC showfilestats'')  
 
                                        INSERT INTO [Administration].[dbo].[DB_FileUseSize]
                                        ([DBName],[DBFileName],[FileSize],[UseSize],[RecordTime])
                                        select DBName,name as DBFileName,
                                        cast(TotalExtents*64.0/1024 as decimal) N''FileSize'',
                                        cast(UsedExtents*64.0/1024 as decimal) N''UseSize'',
                                        getdate()
                                        from #tmpDB
                                        DROP TABLE #tmpDB'
DECLARE @dbCount INT   
SELECT @dbCount=count(*) FROM   MASTER..SysDatabases
where name =@dbname and version is not null and version<>0
if @dbCount>0                                     
                EXEC (@vSQL)
 
                --現在目的取得@tableName
                SET @rowNo = @rowNo + 1 
    END 
DROP TABLE #depTemp1--清除臨時表
IF OBJECT_ID('tempdb..#Data') IS NOT NULL 
    DROP TABLE #Data
IF OBJECT_ID('tempdb..#DataNew') IS NOT NULL 
    DROP TABLE #DataNew
 
IF OBJECT_ID('tempdb..#tmpData') IS NOT NULL 
    DROP TABLE #tmpData
CREATE TABLE #tmpData
([Database Name] NVARCHAR(500),[Log Size(MB)] real,[Log Space Used(%)] real, [status] bit)    
insert into #tmpData
([Database Name] ,[Log Size(MB)] ,[Log Space Used(%)] , [status])    
EXECUTE ('DBCC SQLPERF(LOGSPACE)')
 
INSERT INTO [Administration].[dbo].[DB_LogFile]
([DBName],[DBLogSize(Mb)],[LogSpaceUsed(%)],[RecordTime])
select [Database Name] ,[Log Size(MB)] ,[Log Space Used(%)],getdate()
from #tmpData
drop table #tmpData

第三段, 將DB的資料筆數, 檔案大小存入Table [DB_Record] 中,


IF OBJECT_ID('tempdb..#depTemp') IS NOT NULL 
    DROP TABLE #depTemp 
 
select DbSerId,dbname into #depTemp
from [Administration].[dbo].[DB_Control] where ServerName=@@servername
 
DECLARE @DbSerId INT
DECLARE @dbname nvarchar(255)
DECLARE @max INT --最大行數   
SELECT @max = MAX(DbSerId) FROM [Administration].[dbo].[DB_Control] where ServerName=@@servername
DECLARE @min INT --最大行數   
SELECT @min = Min(DbSerId) FROM [Administration].[dbo].[DB_Control] where ServerName=@@servername
DECLARE @rowNo INT --行號   
SET @rowNo = @min
Declare @vSQL varchar(max)
 
WHILE @rowNo <= @max  
    BEGIN 
        --此處對每一行要進行的操作的代碼   
                select @DbSerId=DbSerId,@dbname=dbname from #depTemp WHERE DbSerId=@rowNo
                --取得DB為@dbname
                --DB編號  @DbSerId
--print @DbSerId
--print @dbname
SET @vSQL = N'
 
--清除掉暫存的table
IF OBJECT_ID(''tempdb..#Data'') IS NOT NULL 
    DROP TABLE #Data
IF OBJECT_ID(''tempdb..#DataNew'') IS NOT NULL 
    DROP TABLE #DataNew
IF OBJECT_ID(''tempdb..#TableRelate'') IS NOT NULL 
    DROP TABLE #TableRelate
USE ['+@dbname +' ]
 
create table #Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100)) 
--#TableRelate用來裝 tableName和tableOwner的暫存
create table #TableRelate(Table_Qualifier varchar(255),Table_OWNER varchar(100),Table_NAME varchar(255),TABLE_TYPE varchar(100),REMARKS varchar(100))
insert into #TableRelate exec sp_tables
 
declare @name varchar(255) 
declare @tableCount varchar(100) 
--用cur來當取得所有的db name
declare cur cursor  for 
select ''[''+Table_OWNER+''].[''+Table_NAME +'']'' from #TableRelate where Table_Type=''TABLE''
--字串要拼好 [dbo].[DB_Name]
open cur 
fetch next from cur into @name 
 
while @@fetch_status=0
begin 
        --print @name
        insert into #data 
        exec sp_spaceused   @name --計算table資料
        fetch next from cur into @name 
end 
close cur 
deallocate cur --釋放cursor
--把整個#data寫入[Administration].[dbo].[DB_Record]
INSERT INTO [Administration].[dbo].[DB_Record]
(
[DbSerID],
[TableName],[Row],[TableSize],[Reserved],[Index_Size],[Unused],[RecordTime])
select convert(int,'''+convert(nvarchar(255),@DbSerId)+''') as DbSerID,
name as TableName,row as Row,
convert(int,replace(data,''KB'','''')) as TableSize,
convert(int,replace(reserved,''KB'','''')) as Reserved,
convert(int,replace(Index_Size,''KB'','''')) as Index_Size,
convert(int,replace(unused,''KB'','''')) as unused,
getdate() as RecordTime from #data
IF OBJECT_ID(''tempdb..#Data'') IS NOT NULL 
    DROP TABLE #Data
IF OBJECT_ID(''tempdb..#DataNew'') IS NOT NULL 
    DROP TABLE #DataNew
'
DECLARE @dbCount INT   
SELECT @dbCount=count(*) FROM   MASTER..SysDatabases
where name =@dbname and version is not null and version<>0
if @dbCount>0
        EXEC (@vSQL)
        --print (@vSQL)
 
                --現在目的取得@tableName
                SET @rowNo = @rowNo + 1 
    END 
DROP TABLE #depTemp--清除臨時表
IF OBJECT_ID('tempdb..#Data') IS NOT NULL 
    DROP TABLE #Data
IF OBJECT_ID('tempdb..#DataNew') IS NOT NULL 
    DROP TABLE #DataNew

第三段, 直接把剛剛儲存的結果查詢出來吧.

use Administration

select * 
from dbo.DB_Control 
left JOIN DB_Record on DB_Control.DbSerid = DB_Record.DbSerid
LEFT JOIN DB_FileUseSize on DB_Control.DbName = DB_FileUseSize.DbName


select * from dbo.DB_LogFile

江江江江
可以看到各DB和Table的資料了.　

回首頁

阿輝的零碎筆記

想到什麼寫什麼

[SQL] 以SQL Command查詢DB內Table和Column資料

系列文章

標籤雲