How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
KarenQuickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
--
David Portas
SQL Server MVP
--
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegroups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.giganews.com...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegroups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
Karen
Quickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
David Portas
SQL Server MVP
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegro ups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.giganews.com ...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael
|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.
|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegro ups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
KarenQuickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
David Portas
SQL Server MVP
--
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegroups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.gi
ganews.com...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegroups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
KarenQuickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
David Portas
SQL Server MVP
--
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegroups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.gi
ganews.com...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegroups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what tables/views/functions a stored proc uses

Hi All,
Is there any way How to find what tables/views/functions a stored proc
uses.
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***Try sp_depends
Thomas
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:%23VriiaVXFHA.1468@.tk2msftngp13.phx.gbl...
> Hi All,
> Is there any way How to find what tables/views/functions a stored proc
> uses.
> Thanks for your help.
> *** Sent via Developersdex http://www.examnotes.net ***|||Hai
Object that dependent on <<table_name>>
exec sp_MSdependencies N'[dbo].[<<Table_name>>]', null, 1315327
Object that <<table_name>> depends on
exec sp_MSdependencies N'[dbo].[<<Table_name>>]', null, 1053183
Thanks
NR. Harisutarsan
*** Sent via Developersdex http://www.examnotes.net ***|||Thanks. That works very well.
*** Sent via Developersdex http://www.examnotes.net ***sql

How to find Server Collation from SQL 2005 tables/views?

Does anyone know what table, view, etc., contains the SQL Server 2005 server
collation (not database collation) value? When I use SQLDMO it returns an
empty string, yet when I view the SQL Server 2005 instance properties in SQL
Server Management Studio, it correctly shows as SQL_Latin... etc.
I have tried querying the master.sys.sysservers view and msdb.sys.sysservers
view but they both report null as well.
Where can I find the Server Collation property programmatically in SQL 2005?
Thanks experts!Perhaps you are looking for
serverproperty('collation')
Ben Nevarez, MCDBA, OCP
Database Administrator
"Mark Findlay" wrote:

> Does anyone know what table, view, etc., contains the SQL Server 2005 serv
er
> collation (not database collation) value? When I use SQLDMO it returns an
> empty string, yet when I view the SQL Server 2005 instance properties in S
QL
> Server Management Studio, it correctly shows as SQL_Latin... etc.
> I have tried querying the master.sys.sysservers view and msdb.sys.sysserve
rs
> view but they both report null as well.
> Where can I find the Server Collation property programmatically in SQL 200
5?
> Thanks experts!
>|||Perfect! Thanks!
Mark
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:715E7532-7B21-4015-94F7-72237EF02FFA@.microsoft.com...
> Perhaps you are looking for
> serverproperty('collation')
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Mark Findlay" wrote:
>

How to find objects by owner

I have an application wich generates database objects (mostly views) within
the application itself. So I have a bunch of views created by a user who I
have to delete but I can't do it because he is owning objects.
Can I write a query to list al the objects owned by this user?
Regardsselect name from sysobjects where uid = user_id('NameOfOwner')
"Zekske" wrote:
> I have an application wich generates database objects (mostly views) within
> the application itself. So I have a bunch of views created by a user who I
> have to delete but I can't do it because he is owning objects.
> Can I write a query to list al the objects owned by this user?
> Regards|||You could use the inbuilt views - just change 'dbo' to your user...
-- tables and views
select * from information_schema.tables
where table_schema = 'dbo'
-- stored procs and functions
select routine_name from information_schema.routines
where routine_schema = 'dbo'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to find objects by owner

I have an application wich generates database objects (mostly views) within
the application itself. So I have a bunch of views created by a user who I
have to delete but I can't do it because he is owning objects.
Can I write a query to list al the objects owned by this user?
Regards
select name from sysobjects where uid = user_id('NameOfOwner')
"Zekske" wrote:

> I have an application wich generates database objects (mostly views) within
> the application itself. So I have a bunch of views created by a user who I
> have to delete but I can't do it because he is owning objects.
> Can I write a query to list al the objects owned by this user?
> Regards
|||You could use the inbuilt views - just change 'dbo' to your user...
-- tables and views
select * from information_schema.tables
where table_schema = 'dbo'
-- stored procs and functions
select routine_name from information_schema.routines
where routine_schema = 'dbo'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to find objects by owner

I have an application wich generates database objects (mostly views) within
the application itself. So I have a bunch of views created by a user who I
have to delete but I can't do it because he is owning objects.
Can I write a query to list al the objects owned by this user?
Regardsselect name from sysobjects where uid = user_id('NameOfOwner')
"Zekske" wrote:

> I have an application wich generates database objects (mostly views) withi
n
> the application itself. So I have a bunch of views created by a user who I
> have to delete but I can't do it because he is owning objects.
> Can I write a query to list al the objects owned by this user?
> Regards|||You could use the inbuilt views - just change 'dbo' to your user...
-- tables and views
select * from information_schema.tables
where table_schema = 'dbo'
-- stored procs and functions
select routine_name from information_schema.routines
where routine_schema = 'dbo'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to find binding errors to views

I have a view with no table present such as
Create table T1
(Col1 int)
go
Create view V1
as
select * from T1
go
drop table T1
go
select * from V1
I want to be able to run a query against a database that would give me all
the views that are inconsistent as above
I tried dbcc checkdb and dbcc checktable and it doesnt seem to work..I don't think there is a single way to get the list of all the views with
binding errors. Perhaps, one option is to execute sp_refreshview in a
cursor/loop which will error out.
If this is something you'd want to preserve for any future views, perhaps
you should consider using explicit column names in SELECT clauses, two part
naming in FROM clauses and WITH SCHEMABINDING option while creating the
view.
Anith|||I tried the cursor approach but the cursor aborts after it finds the first
violation. How can I let it continue ?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eHi%23IVnjFHA.2444@.TK2MSFTNGP10.phx.gbl...
> I don't think there is a single way to get the list of all the views with
> binding errors. Perhaps, one option is to execute sp_refreshview in a
> cursor/loop which will error out.
> If this is something you'd want to preserve for any future views, perhaps
> you should consider using explicit column names in SELECT clauses, two
part
> naming in FROM clauses and WITH SCHEMABINDING option while creating the
> view.
> --
> Anith
>|||I do a build of the database using the source code using DB Ghost Database
Builder. That way all errors are quickly reported and can therefore be fixed
.
The builder builds objects at around 1000/minute making this a very quick an
d
extremely thorough process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Hassan" wrote:

> I have a view with no table present such as
> Create table T1
> (Col1 int)
> go
> Create view V1
> as
> select * from T1
> go
> drop table T1
> go
> select * from V1
> I want to be able to run a query against a database that would give me all
> the views that are inconsistent as above
> I tried dbcc checkdb and dbcc checktable and it doesnt seem to work..
>
>

How to find binding errors to views

I have a view with no table present such as
Create table T1
(Col1 int)
go
Create view V1
as
select * from T1
go
drop table T1
go
select * from V1
I want to be able to run a query against a database that would give me all
the views that are inconsistent as above
I tried dbcc checkdb and dbcc checktable and it doesnt seem to work..I don't think there is a single way to get the list of all the views with
binding errors. Perhaps, one option is to execute sp_refreshview in a
cursor/loop which will error out.
If this is something you'd want to preserve for any future views, perhaps
you should consider using explicit column names in SELECT clauses, two part
naming in FROM clauses and WITH SCHEMABINDING option while creating the
view.
Anith|||I tried the cursor approach but the cursor aborts after it finds the first
violation. How can I let it continue ?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eHi%23IVnjFHA.2444@.TK2MSFTNGP10.phx.gbl...
> I don't think there is a single way to get the list of all the views with
> binding errors. Perhaps, one option is to execute sp_refreshview in a
> cursor/loop which will error out.
> If this is something you'd want to preserve for any future views, perhaps
> you should consider using explicit column names in SELECT clauses, two
part
> naming in FROM clauses and WITH SCHEMABINDING option while creating the
> view.
> --
> Anith
>|||I do a build of the database using the source code using DB Ghost Database
Builder. That way all errors are quickly reported and can therefore be fixed
.
The builder builds objects at around 1000/minute making this a very quick an
d
extremely thorough process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Hassan" wrote:

> I have a view with no table present such as
> Create table T1
> (Col1 int)
> go
> Create view V1
> as
> select * from T1
> go
> drop table T1
> go
> select * from V1
> I want to be able to run a query against a database that would give me all
> the views that are inconsistent as above
> I tried dbcc checkdb and dbcc checktable and it doesnt seem to work..
>
>

How to find binding errors to views

I have a view with no table present such as
Create table T1
(Col1 int)
go
Create view V1
as
select * from T1
go
drop table T1
go
select * from V1
I want to be able to run a query against a database that would give me all
the views that are inconsistent as above
I tried dbcc checkdb and dbcc checktable and it doesnt seem to work..
I don't think there is a single way to get the list of all the views with
binding errors. Perhaps, one option is to execute sp_refreshview in a
cursor/loop which will error out.
If this is something you'd want to preserve for any future views, perhaps
you should consider using explicit column names in SELECT clauses, two part
naming in FROM clauses and WITH SCHEMABINDING option while creating the
view.
Anith
|||I tried the cursor approach but the cursor aborts after it finds the first
violation. How can I let it continue ?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eHi%23IVnjFHA.2444@.TK2MSFTNGP10.phx.gbl...
> I don't think there is a single way to get the list of all the views with
> binding errors. Perhaps, one option is to execute sp_refreshview in a
> cursor/loop which will error out.
> If this is something you'd want to preserve for any future views, perhaps
> you should consider using explicit column names in SELECT clauses, two
part
> naming in FROM clauses and WITH SCHEMABINDING option while creating the
> view.
> --
> Anith
>
|||I do a build of the database using the source code using DB Ghost Database
Builder. That way all errors are quickly reported and can therefore be fixed.
The builder builds objects at around 1000/minute making this a very quick and
extremely thorough process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Hassan" wrote:

> I have a view with no table present such as
> Create table T1
> (Col1 int)
> go
> Create view V1
> as
> select * from T1
> go
> drop table T1
> go
> select * from V1
> I want to be able to run a query against a database that would give me all
> the views that are inconsistent as above
> I tried dbcc checkdb and dbcc checktable and it doesnt seem to work..
>
>

How to find binding errors to views

I have a view with no table present such as
Create table T1
(Col1 int)
go
Create view V1
as
select * from T1
go
drop table T1
go
select * from V1
I want to be able to run a query against a database that would give me all
the views that are inconsistent as above
I tried dbcc checkdb and dbcc checktable and it doesnt seem to work..I don't think there is a single way to get the list of all the views with
binding errors. Perhaps, one option is to execute sp_refreshview in a
cursor/loop which will error out.
If this is something you'd want to preserve for any future views, perhaps
you should consider using explicit column names in SELECT clauses, two part
naming in FROM clauses and WITH SCHEMABINDING option while creating the
view.
--
Anith|||I tried the cursor approach but the cursor aborts after it finds the first
violation. How can I let it continue ?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eHi%23IVnjFHA.2444@.TK2MSFTNGP10.phx.gbl...
> I don't think there is a single way to get the list of all the views with
> binding errors. Perhaps, one option is to execute sp_refreshview in a
> cursor/loop which will error out.
> If this is something you'd want to preserve for any future views, perhaps
> you should consider using explicit column names in SELECT clauses, two
part
> naming in FROM clauses and WITH SCHEMABINDING option while creating the
> view.
> --
> Anith
>|||I do a build of the database using the source code using DB Ghost Database
Builder. That way all errors are quickly reported and can therefore be fixed.
The builder builds objects at around 1000/minute making this a very quick and
extremely thorough process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Hassan" wrote:
> I have a view with no table present such as
> Create table T1
> (Col1 int)
> go
> Create view V1
> as
> select * from T1
> go
> drop table T1
> go
> select * from V1
> I want to be able to run a query against a database that would give me all
> the views that are inconsistent as above
> I tried dbcc checkdb and dbcc checktable and it doesnt seem to work..
>
>