Using FROM @variable in SELECT statement...
-
Hi there,
I'm trying to get info from every usertable in a database; let's say the number of records in each table with count(*). I can do this in a cursor, but I am searching for a way to do this in a SET-statement, like SELECT COUNT(*) FROM @TableName.
Is this even possible?
Greetz,
Hans Brouwer -
I'd saved the following "use of undocumented stored procedure" a long time back and unfortunately did not save the site link so cannot give credit where it's due...
create table #rowcount (tablename varchar(128), rowcnt int) exec sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?' select * from #rowcount order by tablename drop table #rowcount
**ASCII stupid question, get a stupid ANSI !!!** -
But still uses a cursor
If the number of tables is not too large then you could use dynamic sql
DECLARE @sql nvarchar(4000)
SELECT @sql = COALESCE(@sql+' UNION SELECT '''+TABLE_NAME+''',COUNT(*) FROM '+TABLE_NAME,
'SELECT '''+TABLE_NAME+''',COUNT(*) FROM '+TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME <> 'dtproperties'
EXEC(@sql)
But I think sushila's post is the better way
Far away is close at hand in the images of elsewhere.
Anon. -
Man, Ten, I would never had gotten this together! Tnx, I think I can work from this script.
Sushila, tnx too. I should have thought of the sp_forEach... procedures myself, I have looked into it int hte past.
Tnx again both,
Greetz,
Hans Brouwer -
Man, Ten - serves me jolly well right for not reading something through fully -
Thanks for coming to my rescue!
**ASCII stupid question, get a stupid ANSI !!!** -
How about this
exec sp_MSforeachtable 'EXEC ( ''SELECT TableName = ''''?'''', Rows = COUNT(*) FROM ?'')'
Regards,
gova -
well - I did think of that first and discarded it only because the result set isn't neatly tabulated...
**ASCII stupid question, get a stupid ANSI !!!** -
ANSI is :
CREATE TABLE Tables (TableName varchar(128), Rows int)
EXEC sp_MSforeachtable 'EXEC (''INSERT INTO Tables (TableName,Rows) SELECT TableName = ''''?'''', Rows = COUNT(*) FROM ?'')'
SELECT * FROM Tables
DROP TABLE Tables
Andy
-
There's quite nothing like being a minimalist is there ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
I'll juist add mine because if you are going to user cursors AND dynamic SQL, you might as well do them both at the same time.
Declare @TabName sysname,
@SQL nvarchar(4000)
Create Table ##TableRowCount
(TabName sysname primary key,
[Count] bigint)
Declare foo Cursor For
Select name from sysobjects where ObjectProperty(id,'IsUserTable') = 1
order by UPPER(name)
open foo
Fetch next from foo into @TabName
While @@Fetch_Status = 0
BEGIN
Select @sql = N'Insert ##TableRowCount (TabName,[Count])
Select ''' + @TabName + ''', count(*) from ' + @TabName
EXEC sp_executesql @sql
Fetch next from foo into @TabName
END
close foo
deallocate foo
Select * from ##TableRowCount
Drop Table ##TableRowCount
-
The original requester pointed out the he wanted to do it without a cursor. As David pointed out, the underlying code for sp_MSForEachTable and other similar routines are riddled with cursor logic. Using such calls in "cursorless logic" is a bit of an oxymoron.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Dang !
When you nest sp_MSforeachtable within sp_MsForEachDb, I get the below message for each database:
Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'hCForEach' already exists.
create table #rowcount (DatabaseName nvarchar(128) , TAbleName Nvarchar(128), rowcnt int)
exec sp_MsForEachDb
@Command1 = 'exec sp_MSforeachtable ''insert into #rowcount select ''''^'''', ''''?'''', count(*) from ?'''
, @replacechar = '^'
select * from #rowcount
order by DatabaseName, Tablename
drop table #rowcount
SQL = Scarcely Qualifies as a Language
-
Get the list of database names from Master.dbo.SysDataBases... build your own loop or (yeeach!) cursor to step the the DB names or ID's as the outer nest for sp_MSForEachTable.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The rowcnt field in sysindexes will give you a rowcount:
select object_name(id), rowcnt from sysindexes where indid < 2
This is frequently inaccurate, although it's usually a good estimate. If you want the correct answer and are willing to run "select count(*)" on every table, you could instead use "DBCC UPDATEUSAGE (0) WITH COUNT_ROWS, NO_INFOMSGS" before the above query.
If you're going to take the time to count every row, DBCC UPDATEUSAGE has the advantage of saving the results in sysindexes for use by the query optimizer.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply