SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Table Counts
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TRACEYSQL
Aged Yak Warrior

593 Posts

Posted - 04/24/2014 :  16:44:29  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/25/2014 :  10:33:26  Show Profile  Reply with Quote
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

Edited by - TG on 04/25/2014 10:37:16
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

593 Posts

Posted - 04/25/2014 :  23:22:34  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
Sql job,I then just execute the .sql, that should work.
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

593 Posts

Posted - 04/25/2014 :  23:22:41  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
Sql job,I then just execute the .sql, that should work.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000