Query to check if DB name is referred to

Is is possible to construct a query that will list all the references to the
database name that are used throughout the database itself. E.g. It would
tell give me a list of all the views/stored procedures/triggers that refer
to the database name in them. Example:
CREATE PROCEDURE [dbo].[InsertCode]
(@.Code_1 [varchar](10),
@.Description_2 [varchar](50))
AS INSERT INTO [MyDatabase].[dbo].[Codes]
([Code],
[Description])
VALUES
(@.Code_1,
@.Description_2)
GO
This stored procedure explicitly refers to the database name "MyDatabase".
The reason why I want something like this is just in case someone has a
second copy of the database which is called "MyDatabase2". I need to know
that some stored procedures etc are referring to the Database name
incorrectly.
Is this possible?Run sp_who
Madhivanan|||Try,
declare @.s sysname
set @.s = 'my_database'
select distinct
object_name([id]) as obj_name,
case
when objectproperty([id], 'IsProcedure') = 1 then 'Procedure'
when objectproperty([id], 'IsTrigger') = 1 then 'Trigger'
when objectproperty([id], 'IsView') = 1 then 'View'
end as obj_type
from
syscomments
where
(
objectproperty([id], 'IsProcedure') = 1
or objectproperty([id], 'IsTrigger') = 1
or objectproperty([id], 'IsView') = 1
)
and [text] like '%' + @.s + '%'
order by
2, 1
go
AMB
"Chris" wrote:

> Is is possible to construct a query that will list all the references to t
he
> database name that are used throughout the database itself. E.g. It would
> tell give me a list of all the views/stored procedures/triggers that refer
> to the database name in them. Example:
> CREATE PROCEDURE [dbo].[InsertCode]
> (@.Code_1 [varchar](10),
> @.Description_2 [varchar](50))
> AS INSERT INTO [MyDatabase].[dbo].[Codes]
> ([Code],
> [Description])
> VALUES
> (@.Code_1,
> @.Description_2)
> GO
>
> This stored procedure explicitly refers to the database name "MyDatabase".
> The reason why I want something like this is just in case someone has a
> second copy of the database which is called "MyDatabase2". I need to know
> that some stored procedures etc are referring to the Database name
> incorrectly.
> Is this possible?
>
>|||You can search in INFORMATION_SCHEMA.ROUTINES:
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%pubs%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Chris" <cw@.community.nospam> wrote in message news:%23BM$ttxIFHA.3788@.tk2msftngp13.phx.gbl
..
> Is is possible to construct a query that will list all the references to t
he database name that
> are used throughout the database itself. E.g. It would tell give me a lis
t of all the
> views/stored procedures/triggers that refer to the database name in them.
Example:
> CREATE PROCEDURE [dbo].[InsertCode]
> (@.Code_1 [varchar](10),
> @.Description_2 [varchar](50))
> AS INSERT INTO [MyDatabase].[dbo].[Codes]
> ([Code],
> [Description])
> VALUES
> (@.Code_1,
> @.Description_2)
> GO
>
> This stored procedure explicitly refers to the database name "MyDatabase".
> The reason why I want something like this is just in case someone has a se
cond copy of the
> database which is called "MyDatabase2". I need to know that some stored p
rocedures etc are
> referring to the Database name incorrectly.
> Is this possible?
>|||If this is a "one off" process, I would consider scripting the database and
doing a search in an ASCII editor. If you need this process to be done over
and over again, a query like this will replace:
UPDATE syscomments SET text = REPLACE(text, 'MyDatabase2', 'MyDatabase')
WHERE syscomments like '%MyDatabase2%'
NOTE: The above query is written on the fly. As such, it is not tested.
Also, make sure you secure the sproc you embed this in so only DBAs can run
it, as it pokes at metadata tables. If you simply want to see which sprocs d
o
this, you can link back to sysobjects and pull the name for every row
(syscomments) that has MyDatabase2.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Chris" wrote:

> Is is possible to construct a query that will list all the references to t
he
> database name that are used throughout the database itself. E.g. It would
> tell give me a list of all the views/stored procedures/triggers that refer
> to the database name in them. Example:
> CREATE PROCEDURE [dbo].[InsertCode]
> (@.Code_1 [varchar](10),
> @.Description_2 [varchar](50))
> AS INSERT INTO [MyDatabase].[dbo].[Codes]
> ([Code],
> [Description])
> VALUES
> (@.Code_1,
> @.Description_2)
> GO
>
> This stored procedure explicitly refers to the database name "MyDatabase".
> The reason why I want something like this is just in case someone has a
> second copy of the database which is called "MyDatabase2". I need to know
> that some stored procedures etc are referring to the Database name
> incorrectly.
> Is this possible?
>
>|||Thanks to all who have responded.
Initially I just want this to be a simple check as our system loads. We are
going to allow users to have a second copy of our database which they can
use for test purposes.
I am trying to not refer to the database name explicitly anywhere within the
database, but if I have I want to stop the system from loading. Otherwise
we will have users updating the live database when they actually intended
updating the test database.
So initally this will be a safety precaution, but I may eventually update
this to use Gregory's suggestion.
Thanks,
Chris
"Chris" <cw@.community.nospam> wrote in message
news:%23BM$ttxIFHA.3788@.tk2msftngp13.phx.gbl...
> Is is possible to construct a query that will list all the references to
> the database name that are used throughout the database itself. E.g. It
> would tell give me a list of all the views/stored procedures/triggers that
> refer to the database name in them. Example:
> CREATE PROCEDURE [dbo].[InsertCode]
> (@.Code_1 [varchar](10),
> @.Description_2 [varchar](50))
> AS INSERT INTO [MyDatabase].[dbo].[Codes]
> ([Code],
> [Description])
> VALUES
> (@.Code_1,
> @.Description_2)
> GO
>
> This stored procedure explicitly refers to the database name "MyDatabase".
> The reason why I want something like this is just in case someone has a
> second copy of the database which is called "MyDatabase2". I need to know
> that some stored procedures etc are referring to the Database name
> incorrectly.
> Is this possible?
>