|
|
Retrieve All Database Tables in SQL Server Group using T-SQL OpenRowSet Query
In this t-sql tutorial, I want to share with sql developers a sql script which connects to a list of MS SQL Server instances using OpenRowSet SQL Ad Hoc Distributed query.
Within OpenRowSet queries the list of databases installed on each SQL Server instance is selected.
Later the list of databases is used to select all user tables in each database and return to the executing user as a result set.
If you take a look at the t-sql script, you can realize that the list of Microsoft SQL Server instances are stored in a string variable seperated with a delimeter.
I use sql split to create a list of SQL Server instances just like an array.
You can find many t-sql split function examples. Here is a short list of sql split function that you can use.
Recursive Function Sample - MS SQL Server Recursive T-SQL Split Function
Split String using XML - How to Convert or Split a Delimited String Values to Rows using T-SQL XML Commands
T-SQL Split User Defined Function
I could use sql cursor in the tsql script.
But I chosed ROW_NUMBER and WHILE loop structures in t-sql codes.
In each select statement, I stored the return result set in temp tables.
The sql OpenRowSet query uses trusted connection instead of using sql login name and login password pair for authentication.
But OpenRowSet SQL Ad Hoc Distributed query can be altered to use sql user name and password for authentication.
Note that before you use openrowset sql query, you should enable Ad Hoc Distributed Queries by using sp_configure SQL Server configuration procedures.
create table #dbs(cntr int identity(1,1), dbname sysname);
create table #tbl(
servername sysname null,
dbname sysname null,
tablename sysname
);
DECLARE @cntr int, @cntr2 int;
declare @SQLServerGroup nvarchar(max) = 'sqlserver1,sqlserver2,sqlserver3'
declare @sql1 nvarchar(400) = 'SERVER={0};Trusted_connection=yes;'
declare @sql2 nvarchar(400)
declare @sqlors nvarchar(max)
declare @sqldb nvarchar(400) = 'SELECT name FROM sys.databases'
declare @sqldbt nvarchar(400) = 'SELECT name FROM {0}.sys.tables'
declare @sqldbt2 nvarchar(400);
--set SQLServerGroup
select
rn = ROW_NUMBER() over (order by val), val into #t
from dbo.SQLSPLIT(@SQLServerGroup,0,0)
declare @rn int = (select MAX(rn) from #t)
while @rn > 0
begin
SET @sql2 = REPLACE(@sql1, '{0}', (select val from #t where rn = @rn))
select @sqlors = '
SELECT RS.*
FROM OPENROWSET(
''SQLNCLI'', ' +
'''' + @sql2 + ''',
''' + @sqldb + '''
) AS RS;'
insert into #dbs(dbname) exec sp_executesql @sqlors;
select @cntr2 = 1, @cntr = MAX(cntr) from #dbs;
while @cntr2 <= @cntr
begin
SET @sqldbt2 = REPLACE(@sqldbt, '{0}', (select dbname from #dbs where cntr = @cntr2));
select @sqlors = '
SELECT RS.*
FROM OPENROWSET(
''SQLNCLI'', ' +
'''' + @sql2 + ''',
''' + @sqldbt2 + '''
) AS RS;'
insert into #tbl(tablename) exec sp_executesql @sqlors;
update #tbl
set
servername = (select val from #t where rn = @rn),
dbname = (select dbname from #dbs where cntr = @cntr2)
where servername is null and dbname is null;
set @cntr2 = @cntr2 + 1;
end;
select * from #dbs
truncate table #dbs
set @rn = @rn - 1;
end
select * from #tbl
drop table #tbl
drop table #dbs
drop table #t
I believe the above t-sql code can be improved and can be added more functionalities and exception handling using TRY...CATCH methods.
|
Related SQL Resources
SQL Server Articles
SQL Server Tools
SQL Blog
SQL Server 2008 Blog
Certification Exams Blog
Reporting Services Blog
Analysis Services Blog
MS SQL Server Forums
|