August 30, 2006 at 7:42 pm
Here is the general idea.
I need to get (and store for trending) size of every table in every database (for every server). This needs to be retained in a table to be queried against. I have two out of three of these things covered...
I have every server (Job that runs daily that populates the table - linked server together to get all the data together)
I have ever table...here is the basic run down:
Create a temp table, then run sp_msforeachtable inserting into the temp table - Then, a few updates to make the data readable, then insert the data into the final storage table. This works, very well...except that it only works for the database this runs on...it cannot run on other databases, even if the use <database> command. here is the basic SQL:
create
procedure GetTableData1 as
create
table #tmpTableData (
Server varchar(80) default @@servername,
DatabaseName
varchar(80) null,
DateRun
varchar(25) default getdate(),
Name varchar(60) not null,
Rows
int not null,
Reserved
varchar(20) not null,
Data
varchar(20) not null,
Index_Size
varchar(20) not null,
Unused
varchar(20) not null
)
exec
sp_msforeachtable "insert into #tmpTableData (Name, Rows, Reserved, Data, Index_Size, Unused) exec sp_spaceused '?'"
update
#tmpTableData
set
Reserved = left(Reserved, len(Reserved) - 3), Data = left(Data, len(Data) - 3),
Index_Size
= left(Index_Size, len(Index_Size) - 3), Unused = left(Unused, len(Unused) - 3)
alter
table #tmpTableData
alter column Reserved int
alter
table #tmpTableData
alter column Data int
alter
table #tmpTableData
alter column Index_Size int
alter
table #tmpTableData
alter column Unused int
update
#tmpTableData
set [databasename] = db_name()
where [databasename] is null
IF
EXISTS (SELECT name FROM sysobjects WHERE name = 'DB_Table_Info' AND type = 'U')
begin
Insert
into DB_Table_Info
select
* from #tmpTableData
order
by Reserved desc
end
else
begin
select
* into DB_Table_Info from #tmpTableData order by Reserved desc
end
drop
table #tmpTableData
I even tried sp_msforeachdb "GetTableData1", but that does not work...the closest I came was getting 5 copies of each row, one for each database.
Maybe I have been working on this to hard all day, and missed something totally obvious...that would not bother me anymore...
TIA
Cory
-- Cory
September 4, 2006 at 8:00 am
This was removed by the editor as SPAM
September 4, 2006 at 9:22 am
Look at the following thread.....
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=295213
--Ramesh
--Ramesh
September 4, 2006 at 9:44 am
Thank you Ramesh, I look forward to trying that tomorrow!
C
-- Cory
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply