[SQL]註冊本機伺服器群組用於管理多台資料庫伺服器

2012-08-17

1881
0
SQL

紀錄一下過程

註冊本機伺服器群組步驟

1. 選擇註冊的伺服器

2. 「新增伺服器群組」

3. 輸入名稱後按「確定」

4. 「新增伺服器註冊」

5. 輸入伺服器相關資訊，記得勾選「記住密碼」，測試可以連通後按「確定」

6. 將所有的伺服器註冊後，點選「新增查詢」

7. 在查詢頁面可以看到以連結的伺服器總數

8. 輸入要查詢的SQL，可以看到查詢結果合併為同一張表

使用的SQL(統計資料庫表格總筆數)


USE master
GO
 
CREATE Table ##CountTotalNum ( [資料庫名稱] sysname, [資料表名稱] sysname, [筆數] sysname)
DECLARE  @tableNameList TABLE 
(
    counted int,
    tableName varchar(200)           
)
 
INSERT INTO @tableNameList
SELECT row_number() OVER (ORDER BY name) AS rkno
     , name
FROM
    sys.databases
WHERE
    state <> 6
    AND name NOT IN ('master', 'model', 'msdb', 'tempdb')
DECLARE @count int
SELECT @count = count(1)
FROM
    @tableNameList;
 
DECLARE  @tablename nvarchar(200)
DECLARE @dbname varchar(200)
DECLARE @SQLString nvarchar(3000)
 
WHILE @count > 0
BEGIN
SELECT @tablename = tableName
FROM
    @tableNameList
WHERE
    counted = @count
 
SET @dbname = @tablename;
SET @SQLString = '
use [' + @dbname + '];
insert into ##CountTotalNum
SELECT ''' + @dbname + ''' as [資料庫名稱],so.name AS [資料表名稱]
     , max(si.rows) AS [筆數]
 
FROM
    sysobjects AS so
    INNER JOIN sysindexes AS si
        ON object_id(so.name) = si.id
 
WHERE
    so.xtype = ''U''
 
GROUP BY
    so.name
ORDER BY
    [筆數] DESC';
EXEC sp_executesql @SQLString
SELECT @count = @count - 1
End
 
SELECT *
FROM
    ##CountTotalNum
ORDER BY
    [資料庫名稱]
DROP TABLE ##CountTotalNum

結果畫面

回首頁