September 22, 2011 at 2:34 pm
Is there a way to query all tables in all databases
(sql server 2000)
September 22, 2011 at 2:41 pm
Have to ask, why?
September 22, 2011 at 2:53 pm
Our Sr. DBA asked me to look into it.
Basically we migrated a few databases but apparently a few users were still connecting to the old sql instance.
We're trying to find a way to compare the rowcounts in each table against each matching database
September 23, 2011 at 12:25 am
Create a table to store result
create table tempdb..TableRowCounts (db varchar(128), tablename varchar(128), RecCount int primary key (db, tablename))
Count rows in all usertables i all DBs and store in the table create above
declare @SQL varchar(8000)
declare @DB varchar(128)
declare db_cur CURSOR FAST_FORWARD READ_ONLY FOR
select name from master..sysdatabases
open db_cur
FETCH NEXT FROM db_cur INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
set @SQL = 'declare @SQL varchar(8000)' + char(13) +
'DECLARE table_cur CURSOR FAST_FORWARD READ_ONLY FOR' + char(13) +
'select ''insert into tempdb..TableRowCounts select ''''' + @DB + ''''', '''''' + name + '''''', count(*) from '' + ''' + @DB + '..'' + name SQLCode from ' + @DB + '..sysobjects where xtype = ''U''' + char(13) +
'open table_cur' + char(13) +
'FETCH NEXT FROM table_cur INTO @SQL' + char(13) +
'WHILE @@FETCH_STATUS = 0' + char(13) +
'BEGIN' + char(13) +
' exec (@SQL)' + char(13) +
' FETCH NEXT FROM table_cur INTO @SQL ' + char(13) +
'END' + char(13) +
'CLOSE table_cur' + char(13) +
'DEALLOCATE table_cur'
exec (@SQL)
FETCH NEXT FROM db_cur INTO @DB
END
CLOSE db_cur
DEALLOCATE db_cur
Okay second try. The hard part is getting the right amount of ' and at the right places. The result im getting when i run this on my server looks atleast better then before. So i think its okay now.
/T
September 23, 2011 at 5:10 am
You script doesn't work on my server (weird db & table names + case sensitive).
This is overly complex for 2K5+ but since this is sql 2K...
SET STATISTICS IO, TIME OFF
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS ON
GO
USE [master]
GO
IF OBJECT_ID('dbo.spaceused', 'U') IS NULL
BEGIN
CREATE TABLE dbo.spaceused (
DbName sysname DEFAULT(''),
tblName sysname,
Row_count INT ,
Reserved VARCHAR(50),
data VARCHAR(50) ,
index_size VARCHAR(50),
unused VARCHAR(50),
PRIMARY KEY CLUSTERED (DbName, tblName)
);
END
ELSE
BEGIN
--DROP TABLE dbo.spaceused
TRUNCATE TABLE dbo.spaceused
END
COMMIT
GO
DECLARE @Cmd VARCHAR(8000)
SET @Cmd = 'USE [?];
IF ''?'' NOT IN (''tempdb''
--, ''master'', ''model'', ''msdb''
)
BEGIN
--PRINT ''?''
DECLARE @InnerCmd VARCHAR(8000)
SET @InnerCmd = ''
EXEC sp_spaceused '''''' + CHAR(63) + ''''''''
INSERT INTO master.dbo.spaceused(tblName, Row_count,Reserved,data,index_size,unused)
EXEC sp_MSforeachtable @InnerCmd
UPDATE master.dbo.spaceused SET DbName = ''?'' WHERE DbName = ''''
END
'
--PRINT @Cmd
EXEC sp_MSforeachdb @Cmd
DELETE FROM dbo.spaceused WHERE Row_count = 0
SELECT
DbName
, tblName
, Row_count
, CONVERT(BIGINT, REPLACE(Reserved, ' KB', '')) / 1024 AS MB_Reserved
, CONVERT(BIGINT, REPLACE(data, ' KB', '')) / 1024 AS MB_data
, CONVERT(BIGINT, REPLACE(index_size, ' KB', '')) / 1024 AS MB_index_size
, CONVERT(BIGINT, REPLACE(unused, ' KB', '')) / 1024 AS MB_unused
FROM
dbo.spaceused
ORDER BY
DbName
, MB_Reserved DESC
, Row_count DESC
COMMIT
September 23, 2011 at 5:21 am
I get a bunch of
Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'hCForEach' already exists.
messages when i run that code. Maybe its a bug in the sp_MSforeachdb or the sp_MSforeachtable fixed in later versions (and maybe SPs... mine is fairly unpatched). Works well in 2005 though 😀
/T
September 23, 2011 at 5:26 am
tommyh (9/23/2011)
I get a bunch of
Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'hCForEach' already exists.
messages when i run that code. Maybe its a bug in the sp_MSforeachdb or the sp_MSforeachtable fixed in later versions (and maybe SPs... mine is fairly unpatched). Works well in 2005 though 😀
/T
My code?
I really need to get a vm of sql 2K :crazy:
September 23, 2011 at 5:27 am
Ninja's_RGR'us (9/23/2011)
tommyh (9/23/2011)
I get a bunch of
Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'hCForEach' already exists.
messages when i run that code. Maybe its a bug in the sp_MSforeachdb or the sp_MSforeachtable fixed in later versions (and maybe SPs... mine is fairly unpatched). Works well in 2005 though 😀
/T
My code?
I really need to get a vm of sql 2K :crazy:
Yepp on both 😛
/T
September 23, 2011 at 5:31 am
That's what I get for always patching my servers :-D.
September 23, 2011 at 5:54 am
if all you want is row counts, you could use:
exec sp_MSforeachtable 'select count(*), ''?'' as table_name from ?'
September 23, 2011 at 5:57 am
adb2303 (9/23/2011)
if all you want is row counts, you could use:
exec sp_MSforeachtable 'select count(*), ''?'' as table_name from ?'
Gonna be slow as hell to run.
Try that code on a small 50 GB db and see how long it takes before you get complain calls from users ;-).
September 23, 2011 at 6:01 am
Ninja's_RGR'us (9/23/2011)
adb2303 (9/23/2011)
if all you want is row counts, you could use:
exec sp_MSforeachtable 'select count(*), ''?'' as table_name from ?'Gonna be slow as hell to run.
Try that code on a small 50 GB db and see how long it takes before you get complain calls from users ;-).
I'm sure you're right, and it's a quick and dirty way to do it...
having said that, just ran it on a 120GB db and it took 15 seconds...
September 23, 2011 at 6:04 am
Nice san & ram ;-).
Or 1 hell of a wide table.
The problem with it is that you can basically flush out the entire data cache. Slowing everything else way down for a little while.
The other problems is that you don't save the results. So you can't requery that for filtering / ordering and history keeping.
September 23, 2011 at 6:11 am
there might be a cheeky 256GB in that host 😎
Points taken though... thanks
September 23, 2011 at 6:12 am
adb2303 (9/23/2011)
there might be a cheeky 256GB in that host 😎Points taken though... thanks
And only 1 DB :w00t:???
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply