Finding dependency of Objects in SQL Server 2000

Hello,

I would like to know , how can we find the objects dependency in SQL Server.

e.g If there is a Stored Procedure (sp_GetShowEmp) , I need to find On which tables,views,Stored Procedures,Functions is dependent. And Name of the other objects which are dependent on the Stored Procedure (sp_GetShowEmp).

Thanks

Sidheshwar

You can take help of SP_DEPENDS in this case to show the dependancy of other objects on a table or trigger or view.

find out a user/account permission...

is there an easy to find out an user/account's permsions within a database?
thank you
a list of objects and rights.
such as
select delete insert exec
table1 x x
table2 x
stored proc xSteve,
Refer 'sp_helprotect' in BooksOnLine.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> is there an easy to find out an user/account's permsions within a
database?
> thank you
> a list of objects and rights.
> such as
> select delete insert exec
> table1 x x
> table2 x
> stored proc x
>|||can you resend the query w/o using attachement?
my company remove any email attachment from outside automatically. (or if
you can rename the file and change the file extension to .port and resend
it?)
thank you
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> The attached query should work for you, it is based around sysprotects.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> > is there an easy to find out an user/account's permsions within a
> database?
> > thank you
> > a list of objects and rights.
> > such as
> >
> > select delete insert exec
> > table1 x x
> > table2 x
> > stored proc x
> >
> >
>
>|||What follows -- thanks to Mark -- is the script he attached...
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@.REMOVETHISTEXTmetromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> > The attached query should work for you, it is based around sysprotects.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> >
> >
> >
> > "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> > news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> > > is there an easy to find out an user/account's permsions within a
> > database?
> > > thank you
> > > a list of objects and rights.
> > > such as
> > >
> > > select delete insert exec
> > > table1 x x
> > > table2 x
> > > stored proc x
> > >
> > >
> >
> >
> >
>|||I attached it as a file as it's not technically a binary, and also the
formatting gets preserved. Newsreader software tends to chop text, and that
spoils the aesthetics of the code. :-)
Anyway, I've pasted it below for your convenience.
select
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> > The attached query should work for you, it is based around sysprotects.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> >
> >
> >
> > "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> > news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> > > is there an easy to find out an user/account's permsions within a
> > database?
> > > thank you
> > > a list of objects and rights.
> > > such as
> > >
> > > select delete insert exec
> > > table1 x x
> > > table2 x
> > > stored proc x
> > >
> > >
> >
> >
> >
>

find out a user/account permission...

is there an easy to find out an user/account's permsions within a database?
thank you
a list of objects and rights.
such as
select delete insert exec
table1 x x
table2 x
stored proc xSteve,
Refer 'sp_helprotect' in BooksOnLine.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> is there an easy to find out an user/account's permsions within a
database?
> thank you
> a list of objects and rights.
> such as
> select delete insert exec
> table1 x x
> table2 x
> stored proc x
>|||can you resend the query w/o using attachement?
my company remove any email attachment from outside automatically. (or if
you can rename the file and change the file extension to .port and resend
it?)
thank you
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> The attached query should work for you, it is based around sysprotects.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> database?
>
>|||What follows -- thanks to Mark -- is the script he attached...
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@.REMOVETHISTEXTmetromilwaukee
.com
URL http://www.metromilwaukee.com/clintongallagher/

"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
>|||I attached it as a file as it's not technically a binary, and also the
formatting gets preserved. Newsreader software tends to chop text, and that
spoils the aesthetics of the code. :-)
Anyway, I've pasted it below for your convenience.
select
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
>

Find objects not in use.

Hi!
Anyone know of any report or dynamic view where i can find objects
that has never been executed or accessed? I would like to make som
clean up in our database and that would be a really good start :)
Br, OlaHi
check this site
http://www.sqlservercentral.com/columnists/lPeysakhovich/2582.asp
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Stuck" <stuckish@.gmail.com> wrote in message
news:1182412428.873704.81340@.q75g2000hsh.googlegroups.com...
> Hi!
> Anyone know of any report or dynamic view where i can find objects
> that has never been executed or accessed? I would like to make som
> clean up in our database and that would be a really good start :)
> Br, Ola
>

Find objects not in use.

Hi!
Anyone know of any report or dynamic view where i can find objects
that has never been executed or accessed? I would like to make som
clean up in our database and that would be a really good start
Br, OlaHi
check this site
http://www.sqlservercentral.com/col...hovich/2582.asp
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Stuck" <stuckish@.gmail.com> wrote in message
news:1182412428.873704.81340@.q75g2000hsh.googlegroups.com...
> Hi!
> Anyone know of any report or dynamic view where i can find objects
> that has never been executed or accessed? I would like to make som
> clean up in our database and that would be a really good start
> Br, Ola
>

Find object owner

Is there a query I can write that joins 2 system tables to return the owners of objects (e.g. tables) and the object's name?

Thanks,

Dave

IN SQL SERVER 200 the SCHEMA IN the information_VIEW represents the owner OF an objects e.g. FOR tables

SELECT TABLE_SCHEMA FROM [INFORMATION_SCHEMA].TABLES

IN SQL SERVER 2005, you will have TO determine the owner OF the SCHEMA instead using

SELECT Name FROM sys.Schemas

INNER JOIN sys.server_principals

ON [Schemas].principal_id = [server_principals].Principal_id

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

In SQL SERVER 2005, because of the ALTER AUTHORIZATION clause, the schema owner may not always be the object owner

See, http://msdn2.microsoft.com/en-us/library/ms187359.aspx

So, an alternative to the above query for SQL SERVER 2005 is

select user_name(objectproperty(object_id,'OwnerId')), name from sys.objects

This always returns the correct owner of the object and takes 'ALTER AUTHORIZATION' into account.

find invalid views etc.

Hi,
Newbie as I am I cannot find the method to track down invalid objects in a MS SQLServer database.
Looking through documentation and looking at internet does not provide much help.
Please help
WilcoThe only method you find is the one you create using the Mark 1 mod 0 eyeball!!

For instance ... you create and sucessfully compile a sproc. Then you alter the schema of the table and drop a column. The proc knows nothing about the altered table until you try to execute it, at which time SQL Server will throw the error.

Find all the objects(stored procedures) that uses a particular view

Hi,
I need to modify a View. But I don't know how many objects(stored procedure)
in SQL Server uses this view.
Is there a where to find out which objects use this particular view.
I can't do this manually because my DB contains 100's of stored procedures
Thanks
Kiran
you can check the dependancies in EM, or in query analyzer use sp_depends
sp_depends
Displays information about database object dependencies (for example, the
views and procedures that depend on a table or view, and the tables and
views that are depended on by the view or procedure). References to objects
outside the current database are not reported.
Syntax
sp_depends [ @.objname = ] 'object'
Arguments
[@.objname =] 'object'
The database object to examine for dependencies. The object can be a table,
view, stored procedure, or trigger. object is nvarchar(776), with no
default.
Return Code Values
0 (success) or 1 (failure)
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need to modify a View. But I don't know how many objects(stored
procedure)
> in SQL Server uses this view.
> Is there a where to find out which objects use this particular view.
> I can't do this manually because my DB contains 100's of stored procedures
> Thanks
> Kiran
>
|||Hi Simon,
The "sp_depends view_name" returns the list of column names that are in the
view and the related object name, type etc.....
I want to get the names of object that uses this View.
For eg. I have a view vew_employees. There are 4 stored procedures that uses
this view vew_employees.
I need something that will return me the names of these 4 sp.s
Thanks
Kiran
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> you can check the dependancies in EM, or in query analyzer use sp_depends
> sp_depends
> Displays information about database object dependencies (for example, the
> views and procedures that depend on a table or view, and the tables and
> views that are depended on by the view or procedure). References to
> objects
> outside the current database are not reported.
> Syntax
> sp_depends [ @.objname = ] 'object'
> Arguments
> [@.objname =] 'object'
> The database object to examine for dependencies. The object can be a
> table,
> view, stored procedure, or trigger. object is nvarchar(776), with no
> default.
> Return Code Values
> 0 (success) or 1 (failure)
>
> "Kiran Kumar" <nospam@.spam.com> wrote in message
> news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> procedure)
>
|||Assuming your view name is somewhat unique, this will give you an idea:
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%view_name%'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:e4vvuU1HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi Simon,
> The "sp_depends view_name" returns the list of column names that are in
the
> view and the related object name, type etc.....
> I want to get the names of object that uses this View.
> For eg. I have a view vew_employees. There are 4 stored procedures that
uses[vbcol=seagreen]
> this view vew_employees.
> I need something that will return me the names of these 4 sp.s
> Thanks
> Kiran
> "Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
> news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
sp_depends[vbcol=seagreen]
the
>
|||But it does return those 4 procs - within the name column of the output. Is
this not what you are looking for?
In enterprise manager, right click the view, go to "all tasks", and click
"display dependancies...". This will show you the same thing
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:e4vvuU1HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi Simon,
> The "sp_depends view_name" returns the list of column names that are in
the
> view and the related object name, type etc.....
> I want to get the names of object that uses this View.
> For eg. I have a view vew_employees. There are 4 stored procedures that
uses[vbcol=seagreen]
> this view vew_employees.
> I need something that will return me the names of these 4 sp.s
> Thanks
> Kiran
> "Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
> news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
sp_depends[vbcol=seagreen]
the
>

Find all the objects(stored procedures) that uses a particular view

Hi,
I need to modify a View. But I don't know how many objects(stored procedure)
in SQL Server uses this view.
Is there a where to find out which objects use this particular view.
I can't do this manually because my DB contains 100's of stored procedures
Thanks
Kiranyou can check the dependancies in EM, or in query analyzer use sp_depends
sp_depends
Displays information about database object dependencies (for example, the
views and procedures that depend on a table or view, and the tables and
views that are depended on by the view or procedure). References to objects
outside the current database are not reported.
Syntax
sp_depends [ @.objname = ] 'object'
Arguments
[@.objname =] 'object'
The database object to examine for dependencies. The object can be a table,
view, stored procedure, or trigger. object is nvarchar(776), with no
default.
Return Code Values
0 (success) or 1 (failure)
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need to modify a View. But I don't know how many objects(stored
procedure)
> in SQL Server uses this view.
> Is there a where to find out which objects use this particular view.
> I can't do this manually because my DB contains 100's of stored procedures
> Thanks
> Kiran
>|||Hi Simon,
The "sp_depends view_name" returns the list of column names that are in the
view and the related object name, type etc.....
I want to get the names of object that uses this View.
For eg. I have a view vew_employees. There are 4 stored procedures that uses
this view vew_employees.
I need something that will return me the names of these 4 sp.s
Thanks
Kiran
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> you can check the dependancies in EM, or in query analyzer use sp_depends
> sp_depends
> Displays information about database object dependencies (for example, the
> views and procedures that depend on a table or view, and the tables and
> views that are depended on by the view or procedure). References to
> objects
> outside the current database are not reported.
> Syntax
> sp_depends [ @.objname = ] 'object'
> Arguments
> [@.objname =] 'object'
> The database object to examine for dependencies. The object can be a
> table,
> view, stored procedure, or trigger. object is nvarchar(776), with no
> default.
> Return Code Values
> 0 (success) or 1 (failure)
>
> "Kiran Kumar" <nospam@.spam.com> wrote in message
> news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I need to modify a View. But I don't know how many objects(stored
> procedure)
>> in SQL Server uses this view.
>> Is there a where to find out which objects use this particular view.
>> I can't do this manually because my DB contains 100's of stored
>> procedures
>> Thanks
>> Kiran
>>
>|||Assuming your view name is somewhat unique, this will give you an idea:
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%view_name%'
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:e4vvuU1HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi Simon,
> The "sp_depends view_name" returns the list of column names that are in
the
> view and the related object name, type etc.....
> I want to get the names of object that uses this View.
> For eg. I have a view vew_employees. There are 4 stored procedures that
uses
> this view vew_employees.
> I need something that will return me the names of these 4 sp.s
> Thanks
> Kiran
> "Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
> news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> > you can check the dependancies in EM, or in query analyzer use
sp_depends
> >
> > sp_depends
> > Displays information about database object dependencies (for example,
the
> > views and procedures that depend on a table or view, and the tables and
> > views that are depended on by the view or procedure). References to
> > objects
> > outside the current database are not reported.
> >
> > Syntax
> > sp_depends [ @.objname = ] 'object'
> >
> > Arguments
> > [@.objname =] 'object'
> >
> > The database object to examine for dependencies. The object can be a
> > table,
> > view, stored procedure, or trigger. object is nvarchar(776), with no
> > default.
> >
> > Return Code Values
> > 0 (success) or 1 (failure)
> >
> >
> >
> > "Kiran Kumar" <nospam@.spam.com> wrote in message
> > news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> >> Hi,
> >>
> >> I need to modify a View. But I don't know how many objects(stored
> > procedure)
> >> in SQL Server uses this view.
> >>
> >> Is there a where to find out which objects use this particular view.
> >>
> >> I can't do this manually because my DB contains 100's of stored
> >> procedures
> >>
> >> Thanks
> >> Kiran
> >>
> >>
> >
> >
>|||But it does return those 4 procs - within the name column of the output. Is
this not what you are looking for?
In enterprise manager, right click the view, go to "all tasks", and click
"display dependancies...". This will show you the same thing
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:e4vvuU1HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi Simon,
> The "sp_depends view_name" returns the list of column names that are in
the
> view and the related object name, type etc.....
> I want to get the names of object that uses this View.
> For eg. I have a view vew_employees. There are 4 stored procedures that
uses
> this view vew_employees.
> I need something that will return me the names of these 4 sp.s
> Thanks
> Kiran
> "Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
> news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> > you can check the dependancies in EM, or in query analyzer use
sp_depends
> >
> > sp_depends
> > Displays information about database object dependencies (for example,
the
> > views and procedures that depend on a table or view, and the tables and
> > views that are depended on by the view or procedure). References to
> > objects
> > outside the current database are not reported.
> >
> > Syntax
> > sp_depends [ @.objname = ] 'object'
> >
> > Arguments
> > [@.objname =] 'object'
> >
> > The database object to examine for dependencies. The object can be a
> > table,
> > view, stored procedure, or trigger. object is nvarchar(776), with no
> > default.
> >
> > Return Code Values
> > 0 (success) or 1 (failure)
> >
> >
> >
> > "Kiran Kumar" <nospam@.spam.com> wrote in message
> > news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> >> Hi,
> >>
> >> I need to modify a View. But I don't know how many objects(stored
> > procedure)
> >> in SQL Server uses this view.
> >>
> >> Is there a where to find out which objects use this particular view.
> >>
> >> I can't do this manually because my DB contains 100's of stored
> >> procedures
> >>
> >> Thanks
> >> Kiran
> >>
> >>
> >
> >
>

Find all the objects(stored procedures) that uses a particular view

Hi,
I need to modify a View. But I don't know how many objects(stored procedure)
in SQL Server uses this view.
Is there a where to find out which objects use this particular view.
I can't do this manually because my DB contains 100's of stored procedures
Thanks
Kiranyou can check the dependancies in EM, or in query analyzer use sp_depends
sp_depends
Displays information about database object dependencies (for example, the
views and procedures that depend on a table or view, and the tables and
views that are depended on by the view or procedure). References to objects
outside the current database are not reported.
Syntax
sp_depends [ @.objname = ] 'object'
Arguments
[@.objname =] 'object'
The database object to examine for dependencies. The object can be a table,
view, stored procedure, or trigger. object is nvarchar(776), with no
default.
Return Code Values
0 (success) or 1 (failure)
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need to modify a View. But I don't know how many objects(stored
procedure)
> in SQL Server uses this view.
> Is there a where to find out which objects use this particular view.
> I can't do this manually because my DB contains 100's of stored procedures
> Thanks
> Kiran
>|||Hi Simon,
The "sp_depends view_name" returns the list of column names that are in the
view and the related object name, type etc.....
I want to get the names of object that uses this View.
For eg. I have a view vew_employees. There are 4 stored procedures that uses
this view vew_employees.
I need something that will return me the names of these 4 sp.s
Thanks
Kiran
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> you can check the dependancies in EM, or in query analyzer use sp_depends
> sp_depends
> Displays information about database object dependencies (for example, the
> views and procedures that depend on a table or view, and the tables and
> views that are depended on by the view or procedure). References to
> objects
> outside the current database are not reported.
> Syntax
> sp_depends [ @.objname = ] 'object'
> Arguments
> [@.objname =] 'object'
> The database object to examine for dependencies. The object can be a
> table,
> view, stored procedure, or trigger. object is nvarchar(776), with no
> default.
> Return Code Values
> 0 (success) or 1 (failure)
>
> "Kiran Kumar" <nospam@.spam.com> wrote in message
> news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> procedure)
>|||Assuming your view name is somewhat unique, this will give you an idea:
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%view_name%'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:e4vvuU1HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi Simon,
> The "sp_depends view_name" returns the list of column names that are in
the
> view and the related object name, type etc.....
> I want to get the names of object that uses this View.
> For eg. I have a view vew_employees. There are 4 stored procedures that
uses
> this view vew_employees.
> I need something that will return me the names of these 4 sp.s
> Thanks
> Kiran
> "Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
> news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
sp_depends[vbcol=seagreen]
the[vbcol=seagreen]
>|||But it does return those 4 procs - within the name column of the output. Is
this not what you are looking for?
In enterprise manager, right click the view, go to "all tasks", and click
"display dependancies...". This will show you the same thing
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:e4vvuU1HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi Simon,
> The "sp_depends view_name" returns the list of column names that are in
the
> view and the related object name, type etc.....
> I want to get the names of object that uses this View.
> For eg. I have a view vew_employees. There are 4 stored procedures that
uses
> this view vew_employees.
> I need something that will return me the names of these 4 sp.s
> Thanks
> Kiran
> "Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
> news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
sp_depends[vbcol=seagreen]
the[vbcol=seagreen]
>