Hello again, everyone!
I'm attempting to make a stored procedure that gets the rowcount for every
table in a given database. To do this, obviously, I need to use a cursor.
The problem I'm having is with passing the database name to the procedure,
and having it evaluate and run the expression. Here is my code thus far:
----
--
create procedure z_sp_CountTest
@.DatabaseName nvarchar(100)
as
-- Declare the Variables
Declare
@.SQL nvarchar(300),
@.DatabaseSysObjects nvarchar(100),
@.TableName sysname
-- Make a string for the database name and table to get all table names from
Set @.DatabaseSysObjects = @.DatabaseName + '.dbo.sysobjects'
-- Create a SQL string to get the user table names
Set @.SQL = 'Select name from ' + @.DatabaseSysObjects + ' where xtype = ''U''
order by name'
-- Declare the cursor to get the Table Names
Declare GetTableRowCounts Cursor
Local
For
exec master.dbo.sp_executesql @.SQL
Open GetTableRowCounts
Fetch next from GetTableRowCounts into @.TableName
While (@.@.FETCH_STATUS = 0)
Begin
[Do what I want with the rowcounts here.. Will require a statement similiar
to the above]
End
CLOSE GetTableRowCounts
DEALLOCATE GetTableRowCounts
----
--
However, whenever I do a syntax check, I always get:
Server: Msg 156, Level 15, State 1, Procedure z_sp_CountTest, Line 21
Incorrect syntax near the keyword 'exec'.
Can anyone give me a clue as to what it could possibly be?
I've tried almost every itteration of the exec line I could think of. This
includes leaving the master.dbo out, trying just exec, and many other
hair-brainned ideas.
Any help would be great!You'll probably have to dump the results from the @.SQL into a temp table and
run the cursor on the temp table.
-D
"Adam St. Pierre" <AdamStPierre@.discussions.microsoft.com> wrote in message
news:006956CB-2309-457C-BA49-FE08D5BB3894@.microsoft.com...
> Hello again, everyone!
> I'm attempting to make a stored procedure that gets the rowcount for every
> table in a given database. To do this, obviously, I need to use a cursor.
> The problem I'm having is with passing the database name to the procedure,
> and having it evaluate and run the expression. Here is my code thus far:
> ----
--
> create procedure z_sp_CountTest
> @.DatabaseName nvarchar(100)
> as
> -- Declare the Variables
> Declare
> @.SQL nvarchar(300),
> @.DatabaseSysObjects nvarchar(100),
> @.TableName sysname
> -- Make a string for the database name and table to get all table names
> from
> Set @.DatabaseSysObjects = @.DatabaseName + '.dbo.sysobjects'
> -- Create a SQL string to get the user table names
> Set @.SQL = 'Select name from ' + @.DatabaseSysObjects + ' where xtype =
> ''U''
> order by name'
>
> -- Declare the cursor to get the Table Names
> Declare GetTableRowCounts Cursor
> Local
> For
> exec master.dbo.sp_executesql @.SQL
> Open GetTableRowCounts
> Fetch next from GetTableRowCounts into @.TableName
> While (@.@.FETCH_STATUS = 0)
> Begin
> [Do what I want with the rowcounts here.. Will require a statement
> similiar
> to the above]
> End
> CLOSE GetTableRowCounts
> DEALLOCATE GetTableRowCounts
> ----
--
>
> However, whenever I do a syntax check, I always get:
> Server: Msg 156, Level 15, State 1, Procedure z_sp_CountTest, Line 21
> Incorrect syntax near the keyword 'exec'.
> Can anyone give me a clue as to what it could possibly be?
> I've tried almost every itteration of the exec line I could think of. This
> includes leaving the master.dbo out, trying just exec, and many other
> hair-brainned ideas.
>
> Any help would be great!
>|||Adam,
You can use a non-documented sp sp_msforeachtable (not recommended in
production code) or create a cursor to traverse information_schema.tables.
You can also pull that info from the system table sysindexes, but be sure to
first execute "dbcc updateusage (...) with count_rows".
use northwind
go
exec sp_msforeachtable 'select ''?'', count(*) from ?'
go
create table #t (
tname sysname,
rcnt int
)
declare @.tn sysname
declare @.sql nvarchar(4000)
declare my_cursor cursor local fast_forward
for
select
quotename(table_schema) + '.' + quotename(table_name)
from
information_schema.tables
where
objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.tn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'select ''' + @.tn + N''', count(*) from ' + @.tn
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select * from #t order by 1
drop table #t
go
dbcc updateusage (0) with count_rows
go
select object_name([id]) as table_name, rowcnt
from sysindexes
where indid in (0, 1) and objectproperty([id], 'IsUserTable') = 1
order by 1
go
AMB
"Adam St. Pierre" wrote:
> Hello again, everyone!
> I'm attempting to make a stored procedure that gets the rowcount for every
> table in a given database. To do this, obviously, I need to use a cursor.
> The problem I'm having is with passing the database name to the procedure,
> and having it evaluate and run the expression. Here is my code thus far:
> ----
--
> create procedure z_sp_CountTest
> @.DatabaseName nvarchar(100)
> as
> -- Declare the Variables
> Declare
> @.SQL nvarchar(300),
> @.DatabaseSysObjects nvarchar(100),
> @.TableName sysname
> -- Make a string for the database name and table to get all table names fr
om
> Set @.DatabaseSysObjects = @.DatabaseName + '.dbo.sysobjects'
> -- Create a SQL string to get the user table names
> Set @.SQL = 'Select name from ' + @.DatabaseSysObjects + ' where xtype = ''U
''
> order by name'
>
> -- Declare the cursor to get the Table Names
> Declare GetTableRowCounts Cursor
> Local
> For
> exec master.dbo.sp_executesql @.SQL
> Open GetTableRowCounts
> Fetch next from GetTableRowCounts into @.TableName
> While (@.@.FETCH_STATUS = 0)
> Begin
> [Do what I want with the rowcounts here.. Will require a statement simili
ar
> to the above]
> End
> CLOSE GetTableRowCounts
> DEALLOCATE GetTableRowCounts
> ----
--
>
> However, whenever I do a syntax check, I always get:
> Server: Msg 156, Level 15, State 1, Procedure z_sp_CountTest, Line 21
> Incorrect syntax near the keyword 'exec'.
> Can anyone give me a clue as to what it could possibly be?
> I've tried almost every itteration of the exec line I could think of. This
> includes leaving the master.dbo out, trying just exec, and many other
> hair-brainned ideas.
>
> Any help would be great!
>|||Adam,
> Declare GetTableRowCounts Cursor
> Local
> For
> exec master.dbo.sp_executesql @.SQL
When declaring a cursor, a select statement should go after "For". The
"exec" command is not allow here.
AMB
"Adam St. Pierre" wrote:
> Hello again, everyone!
> I'm attempting to make a stored procedure that gets the rowcount for every
> table in a given database. To do this, obviously, I need to use a cursor.
> The problem I'm having is with passing the database name to the procedure,
> and having it evaluate and run the expression. Here is my code thus far:
> ----
--
> create procedure z_sp_CountTest
> @.DatabaseName nvarchar(100)
> as
> -- Declare the Variables
> Declare
> @.SQL nvarchar(300),
> @.DatabaseSysObjects nvarchar(100),
> @.TableName sysname
> -- Make a string for the database name and table to get all table names fr
om
> Set @.DatabaseSysObjects = @.DatabaseName + '.dbo.sysobjects'
> -- Create a SQL string to get the user table names
> Set @.SQL = 'Select name from ' + @.DatabaseSysObjects + ' where xtype = ''U
''
> order by name'
>
> -- Declare the cursor to get the Table Names
> Declare GetTableRowCounts Cursor
> Local
> For
> exec master.dbo.sp_executesql @.SQL
> Open GetTableRowCounts
> Fetch next from GetTableRowCounts into @.TableName
> While (@.@.FETCH_STATUS = 0)
> Begin
> [Do what I want with the rowcounts here.. Will require a statement simili
ar
> to the above]
> End
> CLOSE GetTableRowCounts
> DEALLOCATE GetTableRowCounts
> ----
--
>
> However, whenever I do a syntax check, I always get:
> Server: Msg 156, Level 15, State 1, Procedure z_sp_CountTest, Line 21
> Incorrect syntax near the keyword 'exec'.
> Can anyone give me a clue as to what it could possibly be?
> I've tried almost every itteration of the exec line I could think of. This
> includes leaving the master.dbo out, trying just exec, and many other
> hair-brainned ideas.
>
> Any help would be great!
>|||> I've been told that sometimes this method isn't completely up to date, but I've come acro
ss a
> situation like that yet.
There are plenty such operations and situations, but it has been improved wi
th each version. But in
2005, it should be considered an exception of this isn't up to date. Below i
s a scrip to show this,
and note that even regular INSERTs can make the values go out of whack. On 2
005 the information is
up to date, though:
-- Pre stuff
SET NOCOUNT ON
USE Credit
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
CREATE TABLE #tmp (seq int IDENTITY (1, 1), RowsInTable int, Comment varchar
(200), indid smallint,
dpages int, reserved int, used int, rowcnt int, statblob image )
GO
---
-- INSERT
---
IF OBJECT_ID('charge2') IS NOT NULL DROP TABLE charge2
SELECT * INTO charge2 FROM charge WHERE 1=0
CREATE CLUSTERED INDEX charge_member_link ON dbo.charge2(member_no)
CREATE INDEX charge_category_link ON dbo.charge2(category_no)
CREATE INDEX charge_charge_amt ON dbo.charge2(charge_amt)
INSERT #tmp SELECT (SELECT COUNT(*) FROM charge2), 'Table created with SELEC
T INTO, index created',
indid, dpages, reserved, used, rowcnt, statblob
FROM sysindexes WHERE id = OBJECT_ID('charge2') order by indid
GO
-- Regular INSERTs
DECLARE @.c INT SELECT @.c = 0
WHILE @.c < 5000
BEGIN
INSERT charge2(member_no, provider_no, category_no, charge_dt, charge_amt, s
tatement_no,
charge_code)
VALUES( 63, 482, 8, 'Sep 18 1997 10:43AM', 503.00, 20063, ' ')
SELECT @.c = @.c + 1
END
INSERT #tmp SELECT (SELECT COUNT(*) FROM charge2), 'After a bunch of regular
INSERTs', indid,
dpages, reserved, used, rowcnt, statblob
FROM sysindexes WHERE id = OBJECT_ID('charge2') order by indid
GO
-- UPDATE STATISTICS
UPDATE STATISTICS charge2
INSERT #tmp SELECT (SELECT COUNT(*) FROM charge2), 'After UPDATE STATISTICS'
, indid, dpages,
reserved, used, rowcnt, statblob
FROM sysindexes WHERE id = OBJECT_ID('charge2') order by indid
GO
---
-- DELETE
---
-- We have a table with 5000 rows, make sure columns in sysindexes are updat
ed
DBCC UPDATEUSAGE(credit, charge2) WITH COUNT_ROWS
--Regular DELETE
SET ROWCOUNT 4000 DELETE FROM charge2 SET ROWCOUNT 0
INSERT #tmp SELECT (SELECT COUNT(*) FROM charge2), 'After UPDATEUSAGE and DE
LETE of 4000 rows',
indid, dpages, reserved, used, rowcnt, statblob
FROM sysindexes WHERE id = OBJECT_ID('charge2') order by indid
GO
---
-- TRUNCATE TABLE
---
-- Make sure we have updated sysindexes for 5000 rows
IF OBJECT_ID('charge2') IS NOT NULL DROP TABLE charge2
SELECT * INTO charge2 FROM charge
CREATE CLUSTERED INDEX charge_member_link ON dbo.charge2(member_no)
CREATE INDEX charge_category_link ON dbo.charge2(category_no)
CREATE INDEX charge_charge_amt ON dbo.charge2(charge_amt)
DBCC UPDATEUSAGE(credit, charge2) WITH COUNT_ROWS
TRUNCATE TABLE charge2
INSERT #tmp SELECT (SELECT COUNT(*) FROM charge2), 'Had 5000 rows with corre
ct space usages, then
TRUNCATE TABLE', indid, dpages, reserved, used, rowcnt, statblob
FROM sysindexes WHERE id = OBJECT_ID('charge2') order by indid
GO
---
-- INSERT with subselect
---
-- Get a new fresh empty charge2 without identity
IF OBJECT_ID('charge2') IS NOT NULL DROP TABLE charge2
CREATE TABLE dbo.charge2
(charge_no int NOT NULL ,member_no int NOT NULL ,provider_no int NOT NULL, c
ategory_no int NOT
NULL,
charge_dt datetime NOT NULL , charge_amt money NOT NULL ,statement_no int NO
T NULL, charge_code
char(2) NOT NULL)
GO
CREATE CLUSTERED INDEX charge_member_link ON dbo.charge2(member_no)
CREATE INDEX charge_category_link ON dbo.charge2(category_no)
CREATE INDEX charge_charge_amt ON dbo.charge2(charge_amt)
SET ROWCOUNT 5000 INSERT charge2 SELECT * FROM charge SET ROWCOUNT 0
INSERT #tmp SELECT (SELECT COUNT(*) FROM charge2), 'After INSERT with SUBSEL
ECT', indid, dpages,
reserved, used, rowcnt, statblob
FROM sysindexes WHERE id = OBJECT_ID('charge2') order by indid
GO
---
-- SELECT INTO
---
IF OBJECT_ID('charge2') IS NOT NULL DROP TABLE charge2
SELECT * INTO charge2 FROM charge
INSERT #tmp SELECT (SELECT COUNT(*) FROM charge2), 'After SELECT INTO', indi
d, dpages, reserved,
used, rowcnt, statblob
FROM sysindexes WHERE id = OBJECT_ID('charge2') order by indid
GO
---
-- CREATE INDEX
---
IF OBJECT_ID('charge2') IS NOT NULL DROP TABLE charge2
SELECT * INTO charge2 FROM charge
CREATE CLUSTERED INDEX charge_member_link ON dbo.charge2(member_no)
CREATE INDEX charge_category_link ON dbo.charge2(category_no)
CREATE INDEX charge_charge_amt ON dbo.charge2(charge_amt)
INSERT #tmp SELECT (SELECT COUNT(*) FROM charge2), 'After CREATE INDEX', ind
id, dpages, reserved,
used, rowcnt, statblob
FROM sysindexes WHERE id = OBJECT_ID('charge2') order by indid
GO
-- Update the tables representation in sysindexes before creating the other
indexes
DROP INDEX charge2.charge_category_link
DROP INDEX charge2.charge_charge_amt
DROP INDEX charge2.charge_member_link
DBCC UPDATEUSAGE(credit, charge2) WITH COUNT_ROWS
CREATE CLUSTERED INDEX charge_member_link ON dbo.charge2(member_no)
CREATE INDEX charge_category_link ON dbo.charge2(category_no)
CREATE INDEX charge_charge_amt ON dbo.charge2(charge_amt)
INSERT #tmp SELECT (SELECT COUNT(*) FROM charge2), 'Dropped indexes, DBCC UP
DATEUSAGE and created
indexes', indid, dpages, reserved, used, rowcnt, statblob
FROM sysindexes WHERE id = OBJECT_ID('charge2') order by indid
GO
---
-- SLOW BCP
---
IF OBJECT_ID('charge2') IS NOT NULL DROP TABLE charge2
SELECT * INTO charge2 FROM charge
-- BCP out
EXEC master..xp_cmdshell 'bcp credit..charge2 out c:\charge.txt /t"[]" /r\n
/c /T', 'no_output'
-- Empty table with 3 indexes -> slow bcp
TRUNCATE TABLE charge2
CREATE CLUSTERED INDEX charge_member_link ON dbo.charge2(member_no)
CREATE INDEX charge_category_link ON dbo.charge2(category_no)
CREATE INDEX charge_charge_amt ON dbo.charge2(charge_amt)
DBCC UPDATEUSAGE(credit, charge2) WITH COUNT_ROWS
-- BCP in
EXEC master..xp_cmdshell 'bcp credit..charge2 in c:\charge.txt /t"[]" /r\n /
c /T', 'no_output'
INSERT #tmp SELECT (SELECT COUNT(*) FROM charge2), 'After slow BCP into empt
y table', indid, dpages,
reserved, used, rowcnt, statblob
FROM sysindexes WHERE id = OBJECT_ID('charge2') order by indid
GO
---
-- FAST BCP
---
IF OBJECT_ID('charge2') IS NOT NULL DROP TABLE charge2
SELECT * INTO charge2 FROM charge WHERE 1 = 0
-- BCP in
EXEC master..xp_cmdshell 'bcp credit..charge2 in c:\charge.txt /t"[]" /r\n /
c /T /h"TABLOCK"',
'no_output'
INSERT #tmp SELECT (SELECT COUNT(*) FROM charge2), 'After fast BCP into empt
y table', indid, dpages,
reserved, used, rowcnt, statblob
FROM sysindexes WHERE id = OBJECT_ID('charge2') order by indid
GO
SELECT * FROM #tmp WHERE indid IN(0,1) ORDER BY seq, indid
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:xJyYf.47848$wl.40101@.text.news.blueyonder.co.uk...
> Who needs a cursor? How about using
> select
> so.name,
> si.rows
> from sysobjects so
> Join sysindexes si on si.id = so.id
> and si.indid in (0,1)
> where so.xtype = 'U'
>
> I've been told that sometimes this method isn't completely up to date, but
I've come across a
> situation like that yet. Mind you, I've not been looking.
>
> Colin.
>
> "Adam St. Pierre" <AdamStPierre@.discussions.microsoft.com> wrote in messag
e
> news:006956CB-2309-457C-BA49-FE08D5BB3894@.microsoft.com...
>|||<snip>
> There are plenty such operations and situations, but it has been improved
> with each version. But in 2005, it should be considered an exception of
> this isn't up to date. Below is a scrip to show this, and note that even
> regular INSERTs can make the values go out of whack. On 2005 the
> information is up to date, though:
</snip>
I suppose that I'm really lucky to be working soley with SQL2005 now :-)
Colin.
DarrenSQLIS wrote: