CODEWITHSUNDEEP
sql-serversql-server-2008
Whistler
Whistler

Reputation: 1977

How to add column dynamically to temporary table?

I'm trying to get the size of all tables in the list of databases. The problem I have is, that the tables have the same name in each database. How can I add first column into #tmpTableSizes with the database name:

CREATE TABLE #tmpTableSizes
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

insert #tmpTableSizes
EXEC AAABD.dbo.sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
insert #tmpTableSizes
EXEC BBBDB.dbo.sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
insert #tmpTableSizes
EXEC CCCDB.dbo.sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

Upvotes: 0

Views: 181

Answers (2)

A ツ
A ツ

Reputation: 1267

simple,

add a column dbname:

CREATE TABLE #tmpTableSizes
(   dbname varchar(100),
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
);

you'll have to list the columns when doing the insert and update the dbname for all newly inserted rows (these are the only ones with dbname is null)

 insert #tmpTableSizes 
   ( tableName, numberofRows, reservedSize, dataSize, indexSize, unusedSize)
 EXEC AAABD.dbo.sp_MSforeachtable @command1="EXEC sp_spaceused '?'";

 update #tmpTableSizes set dbname='AAABD' where dbname is null;

since you added the idea to do a loop:

 exec sp_msforeachdb 
 'insert #tmpTableSizes 
   ( tableName, numberofRows, reservedSize, dataSize, indexSize, unusedSize)
 EXEC ?.dbo.sp_MSforeachtable @command1=''EXEC ?.dbo.sp_spaceused ''!'' @replacechar=''!''
 ', '?', 
 'update #tmpTableSizes set dbname=''?'' where dbname is null';

not quite sure if i added enough ', you'll have to check

Upvotes: 1

Andrey Korneyev
Andrey Korneyev

Reputation: 26886

Straightforward solution: use one more "buffer" table, first insert into buffer table and then from it into "main" temporary table with specification of dbName 

CREATE TABLE #tmpTableSizes
(
    dbName varchar(100),
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)


CREATE TABLE #tmpTableSizesDB
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

insert #tmpTableSizesDB
EXEC AAABD.dbo.sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

insert into #tempTableSizes
select 'AAABD', * from #tempTableSizesDB

truncate table #tempTableSizesDB

insert #tmpTableSizesDB
EXEC BBBDB.dbo.sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

insert into #tempTableSizes
select 'BBBDB', * from #tempTableSizesDB

truncate table #tempTableSizesDB

insert #tmpTableSizesDB
EXEC CCCDB.dbo.sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

insert into #tempTableSizes
select 'CCCDB', * from #tempTableSizesDB

Or you can do in in loop using dynamic sql:

create table dbo.#DBases (Name nvarchar(100))
insert into dbo.#DBases
select 'AAABD'
union all 
select 'BBBDB'
union all 
select 'CCCDB'


create table dbo.#tmpTableSizes
(
    dbName varchar(100),
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)


create table dbo.#tmpTableSizesDB
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

declare @stmt nvarchar(max), @DBName nvarchar(100)

declare cur cursor for select Name from dbo.#DBases
open cur

fetch next from cur into @DBName

while (@@fetch_status = 0)
begin
    select @stmt =  'truncate table dbo.#tmpTableSizesDB 

                     insert into dbo.#tmpTableSizesDB
                     EXEC ' + @DBName + '.dbo.sp_MSforeachtable @command1="EXEC sp_spaceused ''?''"

                     insert into dbo.#tmpTableSizes
                     select ''' + @DBName + ''', * from dbo.#tmpTableSizesDB'

    exec sp_executesql @stmt = @stmt

    fetch next from cur into @DBName
end

close cur
deallocate cur

drop table dbo.#tmpTableSizesDB
drop table dbo.#DBases 

select * from dbo.#tmpTableSizes

Upvotes: 1

Related Questions