Table Counts - SQL Server Forums

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

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2014-04-25 : 23:22:34

Sql job,I then just execute the .sql, that should work.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2014-04-25 : 23:22:41

Sql job,I then just execute the .sql, that should work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources