Keith,
Try this instead:
SELECT
[Table] = left(object_name(id), 30)
, [Count] = rows
FROM dbo.sysindexes
WHERE
(
indid < 2
AND
objectproperty(id, 'IsUserTable') = 1
AND
left(object_name(id), 30) <> 'dtproperties'
)
ORDER BY [Table]
This uses the index 'metadata' to solve the query. -It's quite fast!
--
Arnie Rowland
Life should NOT be a journey to the grave with the intention of arriving
safely in an attractive and well preserved body, but rather to skid in
sideways, chocolate in one hand, a very fine wine in the other, body
thoroughly used up, mind totally worn out, and screaming "WOO HOO what a
wild and fulfilling RIDE!"
-----Original Message-----
From: Keith R. Pinster [mailto:keith.pinster@xxxxxxxxx]
Sent: Friday, December 24, 2004 8:38 AM
To: padnug@xxxxxxxxxxxxxxx
Subject: [padnug] Using dynamic SQL to set a variable
This is too weird. I'm trying to loop through my tables to get row
counts. I have a cursor which runs through the tables fine. In the
loop, I execute this statement:
SET @rowCount = EXEC ('SELECT COUNT(*) FROM ' + @tableName)
and I get an error saying that there is Incorrect syntax near the
keyword 'EXEC'. However, if I just run the statement:
EXEC ('SELECT COUNT(*) FROM ' + @tableName)
it works fine. Can't I set a variable using dynamic SQL this way? I
was sure I had done it before...
KRP
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------ Yahoo! Groups Sponsor --------------------~-->
$4.98 domain names from Yahoo!. Register anything.
http://us.click.yahoo.com/Q7_YsB/neXJAA/yQLSAA/dpFolB/TM
--------------------------------------------------------------------~->
|
