Introduction
As all of us do when confronted with a problem, the resource of choice is to ‘Google it’.
This is where the plot thickens. Recently I was asked to stage data from numerous databases which were to be loaded into a data warehouse. To make a long story short, I was looking for a manner in which to obtain the table names from each database, to ascertain potential overlap.
As the source data comes from a SQL database created from dumps of a third party product, one could say that there were +/- 95 tables for each database.
Yes I know that first instinct is to use the system stored procedure “exec sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'”.
However, if one stops to think about this, it would be nice to have all the results in a temporary or disc based table; which in itself , implies additional labour.
This said, I decided to ‘re-invent’ the wheel. The full code sample may be found at the bottom of this article.
Define a few temporary tables and variables
declare @SQL varchar(max);
declare @databasename varchar(75)
/*
drop table ##rawdata3
drop table #rawdata1
drop table #rawdata11
*/
-- A temp table to hold the names of my databases
CREATE TABLE #rawdata1
(
database_name varchar(50) ,
database_size varchar(50),
remarks Varchar(50)
)
--A temp table with the same database names as above, HOWEVER using an
--Identity number (recNO) as a loop variable.
--You will note below that I loop through until I reach 25 (see below) as at
--that point the system databases, the reporting server database etc begin.
--1- 24 are user databases. These are really what I was looking for.
--Whilst NOT the best solution,it works and the code was meant as a quick
--and dirty.
CREATE TABLE #rawdata11
(
recNo int identity(1,1),
database_name varchar(50) ,
database_size varchar(50),
remarks Varchar(50)
)
--My output table showing the database name and table name
CREATE TABLE ##rawdata3
(
database_name varchar(75) ,
table_name varchar(75),
)
Insert the database names into a temporary table
I pull the database names using the system stored procedure sp_databases
INSERT INTO #rawdata1
EXEC sp_databases
Go
Insert the results from #rawdata1 into a table containing a record number #rawdata11 so that I can LOOP through the extract
INSERT into #rawdata11
select * from #rawdata1
We now declare 3 more variables:
@kounter is used to keep track of our position within the loop.
@databasename is used to keep track of the’ current ‘ database name being used in the current pass of the loop; as inorder to obtain the tables for that database we need to issue a ‘USE’ statement, an insert command and other related code parts. This is the challenging part.
@sql is a varchar(max) variable used to contain the ‘USE’ statement PLUS the’ insert ‘ code statements.
We now initalize @kounter to 1 .
declare @kounter int;
declare @databasename varchar(75);
declare @sql varchar(max);
set @kounter = 1
The Loop
The astute reader will remember that the temporary table #rawdata11 contains our database names and each ‘database row’ has a record number (recNo). I am only interested in record numbers under 25. I now set the value of the temporary variable @DatabaseName (see below) .Note that I used the row number as a part of the predicate.
Now, knowing the database name, I can create dynamic T-SQL to be executed using the sp_sqlexec stored procedure (see the code in red below).
Finally, after all the tables for that given database have been placed in temporary table ##rawdata3, I increment the counter and continue on.
Note that I used a global temporary table to ensure that the result set persists after the termination of the run.
At some stage, I plan to redo this part of the code, as global temporary tables are not really an ideal solution.
WHILE (@kounter < 25)
BEGIN
select @DatabaseName = database_name from #rawdata11 where recNo = @kounter
set @SQL = 'Use ' + @DatabaseName +
' Insert into ##rawdata3 ' +
+ ' SELECT table_catalog,Table_name FROM information_schema.tables'
exec sp_sqlexec @Sql
SET @kounter = @kounter + 1
END
The full code extract
Here is the full code sample.
declare @SQL varchar(max);
declare @databasename varchar(75)
/*
drop table ##rawdata3
drop table #rawdata1
drop table #rawdata11
*/
CREATE TABLE #rawdata1
(
database_name varchar(50) ,
database_size varchar(50),
remarks Varchar(50)
)
CREATE TABLE #rawdata11
(
recNo int identity(1,1),
database_name varchar(50) ,
database_size varchar(50),
remarks Varchar(50)
)
CREATE TABLE ##rawdata3
(
database_name varchar(75) ,
table_name varchar(75),
)
INSERT INTO #rawdata1
EXEC sp_databases
go
INSERT into #rawdata11
select * from #rawdata1
declare @kounter int;
declare @databasename varchar(75);
declare @sql varchar(max);
set @kounter = 1
WHILE (@kounter < 25)
BEGIN
select @databasename = database_name from #rawdata11 where recNo = @kounter
set @SQL = 'Use ' + @DatabaseName +
' Insert into ##rawdata3 ' +
+ ' SELECT table_catalog,Table_name FROM information_schema.tables'
exec sp_sqlexec @Sql
SET @kounter = @kounter + 1
END
select * from ##rawdata3
where table_name like '%MySales%'
