Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Table Counts

Author  Topic 

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2014-04-24 : 16:44:29
I am on SQL 2008 R2 and my script works by SQL 2008

I am trying to get the counts from one database DB1 and DB2
Then I will confirm the and email the differences.

It sames you cannot do USE DB1 in a stored procedure.

I also want to run from a DBAUDIT database to go get the counts from DB1 and DB2

Any pointers?

CREATE TABLE #temp(
rec_id int IDENTITY (1, 1),
database_name nvarchar(20),
table_name varchar(128),
nbr_of_rows int,
nbr_of_rows_replication int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
percent_of_db decimal(15,12),
db_size decimal(15,2),
time_stamp datetime)
ALTER TABLE [dbo].[#temp] ADD CONSTRAINT [DF_TIME_STAMP] DEFAULT (getdate()) FOR [TIME_STAMP]

-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",
@command2="update #temp set table_name = '?' , database_name = 'Mytable' where rec_id = (select max(rec_id) from #temp)"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-25 : 10:33:26
how about making this a script rather than an SP.

EDIT:
or you could do this:

exec('use DB1;
-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace ''?''",
@command2="update #temp set table_name = ''?'' , database_name = ''DB1'' where rec_id = (select max(rec_id) from #temp)"
')

exec('use DB2;
-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace ''?''",
@command2="update #temp set table_name = ''?'' , database_name = ''DB2'' where rec_id = (select max(rec_id) from #temp)"
')


Be One with the Optimizer
TG
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2014-04-25 : 23:22:34
Sql job,I then just execute the .sql, that should work.
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2014-04-25 : 23:22:41
Sql job,I then just execute the .sql, that should work.
Go to Top of Page
   

- Advertisement -