Making Store Procedure Generic

Making Store Procedure Generic

Consider creating stored procedures in the master database.

Use the special "sp_" prefix for these procedures, and use the undocumented system stored procedure "sp_MS_marksystemobject" to mark the stored procedure as a system stored procedure.

System stored procedures, although residing in the master database, operate on the database objects from where the stored procedure is executed.

All the usual caveats about using undocumented procedures and adding stored procedures to a system database apply.

Example:
***********
CREATE PROCEDURE sp_counts AS

DECLARE @TABLENAME VARCHAR(50)
DECLARE @SQL VARCHAR(200)
create table #temp
(
tablename varchar(300),
noofrows int
)

DECLARE C1 CURSOR FOR
select table_name from information_schema.tables where table_type='base table'

OPEN C1
FETCH NEXT FROM C1 INTO @TABLENAME
WHILE @@FETCH_STATUS=0
BEGIN

SET @SQL = 'select isnull(null,'''+@TABLENAME+'''),rows from sysindexes where indid<2 and id = object_id('+ '''' + @TABLENAME +'''' +')'

insert into #temp exec(@SQL)
FETCH NEXT FROM C1 INTO @TABLENAME
END
close C1
deallocate C1
delete from #temp where tablename = 'dtproperties'
select * from #temp order by noofrows desc
drop table #temp
GO

********
exec sp_MS_marksystemobject 'sp_counts'
********

Now from any database you can give EXEC SP_COUNTS which gives you the tablename and total rows in the table.