Hello Friends, today one of my blog readers asked one
question to me and the question was “how can we get the size of all tables from
all the databases in SQL in one go?”. So I thought that I should write an
article on the same so that all of my blog readers can be benefited.
Below is the T-SQL batch which he was executing. But instead
of getting table size of all the tables from all the databases, it was
returning size of all tables from one database (which was coming in the loop
first) and same results were getting replicated for all the databases. Let me
show the where the problem was?
CREATE TABLE #TableSize
(
tblName varchar(100),
noofRows varchar(100),
reserved_Size varchar(50),
data_Size varchar(50),
index_Size varchar(50),
unused_Size varchar(50)
)--Creation of temporary table
create table #tmpdatabaselist(dbname varchar(100),rowid int identity)--Creation of temporary table
insert into #tmpdatabaselist(dbname) select name from master.dbo.sysdatabases where dbid >4--Inserting list of user databases
declare @dbname varchar(100)
declare
@mincounter as int
declare
@maxcounter as int
declare @strqry as varchar(1000)
select
@maxcounter =MAX(rowid),@mincounter=MIN(rowid) from #tmpdatabaselist
while
@mincounter <=@maxcounter --loop over all database
begin
select @dbname =dbname from
#tmpdatabaselist where rowid=@mincounter
set @strqry='Use ' + @dbname--here we are
trying to change the database selection
exec(@strqry)--as soon as this query
executes, the database selection change it to previous selected database(in which the query was executed)
insert #TableSize
Execute sp_MSforeachtable @command1 ="EXEC sp_spaceused '?'"
set @mincounter=@mincounter +1
end
select * from #TableSize
Problem Area in the above Query: - In
the above query, In while loop, we are trying to change the database selection
(In which the query will be executed) at run time, but the scope of that query which
is being used to change the database is limited to that line only, once the
query gets executed, database selection will changed to the database in which
the above batch was executed.
set @strqry='Use ' + @dbname
exec(@strqry)
Lets take an live example to prove this.
I have two
database encircled with red color in the above picture (ETL and ETLRESTORE),
Now I want to get the list of all tables from sysobjects table where xtype=U(
user tables) . I am going to run below query by selecting ETL database, before I
run the below query I want to show you number of tables present in each
database.
declare @dbname as varchar(20)
set @dbname ='ETL'
exec('use
' +@dbname)
select * from sysobjects where
xtype='U'
set @dbname ='ETLRESTORE'
exec('use
' +@dbname)
select * from sysobjects where xtype ='U'
In ETL
Database, I have “Employee” table and in ETLRESTORE table, I have “Tracker”
table, lets execute the above query and see what is the outcome of the query.
You can see in
the result set, same table is getting repeated, the noticeable thing is that we
have run the above query by selecting the “ETL” database (Encircled by Blue
color), hence it was able to fetch record from the database in which the query
was executed but failed to fetch the records from the “ETLRESTORE” database. I
hope this example has given you the clarity about what wanted to make you
understand. So to overcome this problem lets write same query in different way.
declare @dbname as varchar(20)
set @dbname ='ETL'
exec('use
' +@dbname +
' select * from sysobjects where xtype =''U''')
set @dbname ='ETLRESTORE'
exec('use ' +@dbname
+ ' select * from
sysobjects where xtype =''U''')
I have
concatenated the query which I want to execute, in different databases with “use
database” statement. Hence it will work fine as whole query will be executed in
one go and within scope. Lets run the above query and see the result.
Now we can
see in the result set, we are getting tables from both databases. So the problem
with the batch query can be resolved by replacing the query with below
mentioned query.
CREATE TABLE #TableSize
(
tblName varchar(100),
noofRows varchar(100),
reserved_Size varchar(50),
data_Size varchar(50),
index_Size varchar(50),
unused_Size varchar(50)
)
create table #tmpdatabaselist(dbname
varchar(100),rowid int identity)
insert into #tmpdatabaselist(dbname) select name from master.dbo.sysdatabases where dbid >4
declare @dbname varchar(100)
declare
@mincounter as int
declare
@maxcounter as int
declare @strqry as varchar(1000)
select
@maxcounter =MAX(rowid),@mincounter=MIN(rowid) from #tmpdatabaselist
while
@mincounter <=@maxcounter
begin
select @dbname =dbname from
#tmpdatabaselist where rowid=@mincounter
set @strqry ='Use ' + @dbname + ' insert #TableSize
Execute sp_MSforeachtable @command1
="EXEC sp_spaceused ''?''"'
exec(@strqry)
set @mincounter=@mincounter +1
end
select * from #TableSize
No comments:
Post a Comment