Cycling through all DBs with startup stored proc

[b4_netlogix]

I have the following stored procedure set up as a startup procedure in the master database. I was lead to it from a microsoft SQL server news group and I have found that it is very helpful. I have been told, though, that it only runs on the master database at startup, but I need it to run for all of the databases on my server. (I'm a newbie and can't follow the code myself). How can this stored proc be modified so that it runs on all of the databases on my server at startup.

Any help is greatly appreciated. Thanks in advance!


CREATE PROC sp_checkident AS
/************************************************** ******************/
/* Procedure to run DBCC CHECKIDENT for a database. */
/* Copyright (c) Tibor Karaszi and B÷rje Carlsson 1999 */
/* Tested on version 6.5 and 7.0. */
/************************************************** ******************/
SET NOCOUNT ON

DECLARE @ver VARCHAR(5)
SELECT @ver = CASE
WHEN CHARINDEX('6.50', @@VERSION) > 0 THEN '6.50'
WHEN CHARINDEX('7.00', @@VERSION) > 0 THEN '7.00'
END
-- Remove below IF statement if you want to execute on 7.0
IF @ver = '6.50'
BEGIN
DECLARE tbl_names INSENSITIVE CURSOR FOR
SELECT OBJECT_NAME(sc.id)
FROM syscolumns sc, sysobjects so
WHERE sc.id = so.id
AND convert(bit, sc.status & 0x0080) = 1
AND so.type IN ('U', 'S&#39

OPEN tbl_names
DECLARE @tbl_name VARCHAR(30)
DECLARE @exec_str VARCHAR(255)
FETCH NEXT FROM tbl_names INTO @tbl_name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN SELECT @exec_str = &#39;DBCC CHECKIDENT (&#39;&#39;&#39; + @tbl_name
+ &#39;&#39;&#39 WITH NO_INFOMSGS&#39;
PRINT @exec_str
EXEC( @exec_str)
END
FETCH NEXT FROM tbl_names INTO @tbl_name
END
DEALLOCATE tbl_names
END

GO

[Ed Alexander]

I&#39;ve had the need in the past to run a stored procedure against all databases on a given server and was able to make it happen using the Execute command, a cursor and the master..sysdatabases table.

You would keep your stored procedure as is and store it in the master database. Change it so it is not run automatically at startup.

Then create the following stored proc (rename it how you like) and have it run as the sql server start up.

This procedure creates a list of databases from the sysdatabases table and loops this list executing the sp_checkident procedure for each database. I think this will do what you need.

CREATE PROCEDURE sp_Run_sp_checkident AS

declare @dbname varchar(30)

DECLARE cCur CURSOR FOR
SELECT name From master..sysdatabases

OPEN cCur

FETCH NEXT FROM cCur INTO @DBName

WHILE (@@ERROR = 0 AND @@FETCH_STATUS >= 0)
BEGIN
EXEC (&#34;USE &#34; + @DBName + &#34;
exec sp_checkident &#34; + @dbname)

FETCH NEXT FROM cCur INTO @DBName
END /*while*/


CLOSE cCur
DEALLOCATE cCur
GO

Drop me an email if you need more details. edward.alexander@firstdata.com

------------
b4_netlogix at 6/17/99 9:15:33 AM

I have the following stored procedure set up as a startup procedure in the master database. I was lead to it from a microsoft SQL server news group and I have found that it is very helpful. I have been told, though, that it only runs on the master database at startup, but I need it to run for all of the databases on my server. (I&#39;m a newbie and can&#39;t follow the code myself). How can this stored proc be modified so that it runs on all of the databases on my server at startup.

Any help is greatly appreciated. Thanks in advance!


CREATE PROC sp_checkident AS
/************************************************** ******************/
/* Procedure to run DBCC CHECKIDENT for a database. */
/* Copyright (c) Tibor Karaszi and B÷rje Carlsson 1999 */
/* Tested on version 6.5 and 7.0. */
/************************************************** ******************/
SET NOCOUNT ON

DECLARE @ver VARCHAR(5)
SELECT @ver = CASE
WHEN CHARINDEX(&#39;6.50&#39;, @@VERSION) > 0 THEN &#39;6.50&#39;
WHEN CHARINDEX(&#39;7.00&#39;, @@VERSION) > 0 THEN &#39;7.00&#39;
END
-- Remove below IF statement if you want to execute on 7.0
IF @ver = &#39;6.50&#39;
BEGIN
DECLARE tbl_names INSENSITIVE CURSOR FOR
SELECT OBJECT_NAME(sc.id)
FROM syscolumns sc, sysobjects so
WHERE sc.id = so.id
AND convert(bit, sc.status & 0x0080) = 1
AND so.type IN (&#39;U&#39;, &#39;S&#39

OPEN tbl_names
DECLARE @tbl_name VARCHAR(30)
DECLARE @exec_str VARCHAR(255)
FETCH NEXT FROM tbl_names INTO @tbl_name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN SELECT @exec_str = &#39;DBCC CHECKIDENT (&#39;&#39;&#39; + @tbl_name
+ &#39;&#39;&#39 WITH NO_INFOMSGS&#39;
PRINT @exec_str
EXEC( @exec_str)
END
FETCH NEXT FROM tbl_names INTO @tbl_name
END
DEALLOCATE tbl_names
END

GO