Monthly Archives: March 2010

STORED PROCS: WHEN TO USE EXECUTE COMMAND?

While running stored procedures in SQL Server, you may have noticed that it seems to make no difference whether or not you use the EXECUTE (or EXEC) command before the procedure name. When should you use EXEC – if at all? The Books Online article for Executing Stored Procedures says that EXEC is only needed if more than one stored procedure is run in a batch. This means I can execute one sproc call with or without EXEC:

USE AdventureWorks
GO
sp_helpfile

or:

USE AdventureWorks
GO
EXEC sp_helpfile

But if I need to run more than one sproc at the same time (in a batch), I have to use:

USE AdventureWorks
GO
EXEC sp_spaceused ‘Person.Contact’, true
EXEC sp_helpfile
GO

or just:

EXEC sp_spaceused ‘Person.Contact’, true
EXEC sp_helpfile

Both statements return the dual result set below:

http://msdn.microsoft.com/en-us/library/ms189915.aspx

FIND ROWCOUNT FOR ALL TABLES

Need to find out which of your tables have the highest rowcounts? Pinal Dave submitted a solution on his blog (sqlauthority.com). I’ve modified it to include only user tables:

USE Northwind
GO
SELECT OBJECT_NAME(st.object_id) TableName, st.row_count
FROM sys.dm_db_partition_stats st
join sys.objects ob
on ob.object_id = st.object_id
WHERE st.index_id < 2
and ob.type = ‘U’
ORDER BY st.row_count DESC>

The above script works for SQL Server 2005. For SQL Server 2000, Afonso Mata posted compatible code on Pinal’s blog that I’ve also drawn from. Try:

USE Northwind
GO
SELECT OBJECT_NAME(si.id) TableName, si.rowcnt
FROM sysindexes si
join sysobjects ob
on ob.id = si.id
WHERE OBJECTPROPERTY(si.id,’isUserTable’)=1 AND si.indid < 2
and ob.type = ‘U’
ORDER BY si.rowcnt DESC

http://blog.sqlauthority.com/2009/01/13/sql-server-find-row-count-in-table-find-largest-table-in-database-t-sql/#comment-62037

Randomizing Result Sets with NEWID Article

My second article is published on SQLServerCentral.com: ‘Randomizing Result Sets with NEWID’.  Check it out here!

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: