Sql-server – Referencing a stored procedure by a name description across all databases in a SQL Server 2000 Instance

sql-server-2000ssrsstored-procedures

Does anyone have a valid method in referencing system information to search for a stored procedure across all databases in a SQL Server 2000 instance? I am currently tasked with migrating crystal reports to SSRS and the customer has roughly 500 or more crystal reports with stored procedures spread over multiple databases. I know I can filter each database's programmability folder by text but I was looking for an outcome in which I can query master.dbo.sysdatabases and reference that to syscomments in which I can specify text and find a stored procedure tied to a database.

Best Answer

Just use a cursor to loop through the databases and query the sysobjects table:

Select * from sysobjects 
where type='P' 
and name='StoredProcName'

(Cursor code not included.) Or use the (undocumented) sp_MSforeachdb:

EXEC sp_MSforeachdb 'USE ? SELECT ''?'' as dbname, name FROM sysobjects 
      WHERE type = ''P'' AND name = ''StoredProcName''

(don't split that over two lines, though)

Also, sp_MSforeachdb has known issues, like failing on very long database names, so I usually prefer the cursor.

Related Solutions

Sql-server – SQL Server 2000 : List and Disable all triggers

SQL Server 2000 has ALTER TABLE foo ENABLE TRIGGER ALL too

And to generate the required ALTER TABLE script use this

SELECT
    DISTINCT 'ALTER TABLE ' + OBJECT_NAME(parent_obj) + ' /*DISABLE*/ ENABLE TRIGGER ALL'
FROM
    sysobjects
WHERE
    xtype = 'TR'

I wouldn't bother testing status of the triggers, personally. A disabled trigger should not exist IMHO. Not least because OBJECTPROPERTY does not support it in SQL Server 2000

Edit:

My mistake, OBJECTPROPERTY in SQL Server 2000 has ExecIsTriggerDisabled as per comment. So you can track status before and after

Running a stored procedure across db Link

You will need to add the table to the insert statement...

Insert into schema.table@myLink SELECT * from testSchema;

and re-arrange the procedure call a bit...

schema.procedure@myLink();

You might also consider removing the first commit so that the insert and procedure call are done in the same transaction.

Best Answer

Related Solutions

Sql-server – SQL Server 2000 : List and Disable all triggers

Running a stored procedure across db Link

Related Question