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?
>

query the amount of transactions in a period of time with sql server 2000

Is there a native tool (profile,trace,performance) feature I can use
to determine the amount of transactions that occur throughout the day?
Or is there a system table that keeps track of this(would be
preferable .. less strain on the system)?
I assume figuring out the transaction in a certain period will enable
me to calculate the busiest periods...I need to know the busiest
period of the day...how do I do this without putting an additional
strain on the server (can I use a different machine other than the
server to save a trace) ...I need to determine strain on
(processor,memory, and disk).
I also need to get a count on the largest number of users (running
transactions) on the server simultaneously.
Any help/advice would be deeply appreciatedFor this to be really meaingful, you should first define what you mean by
transactions. Your definition of transactions can impact your count of
transactions per second.
But if you just want to get a rough idea and the number of SQL requests from
non-apps (e.g. your Enterprise Manager, your monitoring tools, your cluster
service, etc)is relatively small compared to the SQL requests from your apps,
the perfmon counter batch Requests/sec under SQLServer:SQL Statistics can
give you pretty good idea as to how busy your SQL instance is and when. And
collecting the values of this counter is inexpensive.
Linchi
"tom booster" wrote:
> Is there a native tool (profile,trace,performance) feature I can use
> to determine the amount of transactions that occur throughout the day?
> Or is there a system table that keeps track of this(would be
> preferable .. less strain on the system)?
> I assume figuring out the transaction in a certain period will enable
> me to calculate the busiest periods...I need to know the busiest
> period of the day...how do I do this without putting an additional
> strain on the server (can I use a different machine other than the
> server to save a trace) ...I need to determine strain on
> (processor,memory, and disk).
> I also need to get a count on the largest number of users (running
> transactions) on the server simultaneously.
>
> Any help/advice would be deeply appreciated
>