Finding column use with syscomments

In SQL 2000, I know that "Display Dependencies" (and probably the
Sysdepends table) are not accurate.
Does the syscomments field for a view always contain the correct and
current view definition?
I need to find all uses of a given field across all views (there are about
150 views). Is looking in syscomments, or Information_Schema, going to be
reliable? Are there better ways? I have looked in Google and haven't
found anything yet. Still looking though...
Thanks.
David WalkerYou can refresh all your views and then use system views
information_schema.columns.
Example:
use northwind
go
declare @.ts sysname
declare @.tn sysname
declare @.sql nvarchar(4000)
declare views_cursor cursor local fast_forward
for
select
table_schema,
table_name
from
information_schema.tables
where
table_type = 'view'
and objectproperty(object_id(quotename(table
_schema) + N'.' +
quotename(table_name)), 'IsMSShipped') = 0
open views_cursor
while 1 = 1
begin
fetch next from views_cursor into @.ts, @.tn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'exec sp_refreshview ''' + quotename(@.ts) + N'.' +
quotename(@.tn) + ''''
exec sp_executesql @.sql
end
close views_cursor
deallocate views_cursor
declare @.cn sysname
set @.cn = 'OrderID'
select
*
from
information_schema.columns
where
objectproperty(object_id(quotename(table
_schema) + N'.' +
quotename(table_name)), 'IsView') = 1
and objectproperty(object_id(quotename(table
_schema) + N'.' +
quotename(table_name)), 'IsMSShipped') = 0
and column_name = @.cn
order by
table_schema,
table_name,
ordinal_position
go
AMB
"DWalker" wrote:

> In SQL 2000, I know that "Display Dependencies" (and probably the
> Sysdepends table) are not accurate.
> Does the syscomments field for a view always contain the correct and
> current view definition?
> I need to find all uses of a given field across all views (there are about
> 150 views). Is looking in syscomments, or Information_Schema, going to be
> reliable? Are there better ways? I have looked in Google and haven't
> found anything yet. Still looking though...
> Thanks.
> David Walker
>|||> Does the syscomments field for a view always contain the correct and
> current view definition?
AFAIK, yes. With one exception. If you use sp_rename to rename a view, the i
nfo in syscomments will
have the old name (in the CREATE VIEW part).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DWalker" <none@.none.com> wrote in message news:eAQxymoSFHA.1384@.TK2MSFTNGP09.phx.gbl...[co
lor=darkred]
> In SQL 2000, I know that "Display Dependencies" (and probably the
> Sysdepends table) are not accurate.
> Does the syscomments field for a view always contain the correct and
> current view definition?
> I need to find all uses of a given field across all views (there are about
> 150 views). Is looking in syscomments, or Information_Schema, going to be
> reliable? Are there better ways? I have looked in Google and haven't
> found anything yet. Still looking though...
> Thanks.
> David Walker[/color]|||And another one is that if you use "select * ..." then you will not find any
column name in the syscomments, but you will in the syscolumns. That is the
reason why after refreshing the view, I selected from
information_schema.columns and not from syscomments.
AMB
"Tibor Karaszi" wrote:

> AFAIK, yes. With one exception. If you use sp_rename to rename a view, the
info in syscomments will
> have the old name (in the CREATE VIEW part).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DWalker" <none@.none.com> wrote in message news:eAQxymoSFHA.1384@.TK2MSFTNG
P09.phx.gbl...
>
>|||"examnotes"
<AlejandroMesa@.discussions.microsoft.com> wrote in
news:B0934023-3D8D-46DE-AA73-C27AC81C8F73@.microsoft.com:

> And another one is that if you use "select * ..." then you will not
> find any column name in the syscomments, but you will in the
> syscolumns. That is the reason why after refreshing the view, I
> selected from information_schema.columns and not from syscomments.
>
> AMB
>
Thanks to you both. I didn't know about refreshing the views. I'll
steal that code from you, AMB, and keep it in my database. There are
times when I want Display Dependencies in EM to give me the right
answer, and it looks like refreshing the views when I need this
information, is the way to go.
Thanks!
Davidsql

Finding all views with a specific name, in all databases

Hi,

We are migrating to SQL Server 2005 from 2000 and we have a view created in many of our databases. That view must be changed after the migration because it uses system tables and does not return the correct information in SQL Server 2005.

To do that, I want to create an SSIS-package that loops through all the databases on a particular server. If that view exists in the database, I want to run a script that change the view.

What I try to do:

Set up a For Each Loop container with Foreach SMO Enumerator

Set the connection to my server

Set the Enumerate property to "SMOEnumObj[@.Name='Databases']/SMOEnumType[@.Name='Names']"

On the Variable Mapping page, place Index 0 in Variable User::dbName

In the For Each Loop, place a script task to msgbox the value of User::dbName

This all works good. The problem comes when I try to nest the For Each Loop

Inside the "For Each Database Loop", place a new For Each Loop container with Foreach SMO Enumerator

I connect the Msgbox Script Task with the new For each loop

Use the same connection

Set the EnumURN property in the Expression Builder to "Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

On the Variable Mapping page, place index 0 in Variable User::tabName

In the For Each Loop, place a script task to msgbox the value of User::tabName

When I try to run the package now, it does not at all.

In my Progress tab I see that Validation has started and completed for each task, but I see now errors. Nothing has run.

Test 1: I change the DelayValidation for the Inner Loop to True

Now the package runs, but I never get to the script task in the inner loop.

Question 1: What's the problem?

Question 2: Is there another way to do this?

Regards

Magnus

Have you tried using a second connection? It seems like the above should work for you, but maybe there's an issue with the SMO Enumerator that requires a different connection for the inner loop.|||

Hi Sean, and thanks for your response.

I tried to use a second connection, but I still get the same result.

/Magnus

|||Forget the SMO enumerator in your Foreach loop.

Do an Execute SQL Task and use something like:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME LIKE 'VIEWNAME%'

Then set the resultset to full result set and create an object type var to hold the results. Then loop thru the results using the ForEach ADO.

See this example. I found it very helpful.

http://www.cubido.at/Blog/tabid/176/EntryID/59/Default.aspx|||

Hi Tom, I tried your suggestion and it worked alright. Thanks!

Because I only expect a single row, I used a Single Row instead of Full ResultSet and used a SQL like

SELECT ISNULL(Max(TABLE_NAME), '') AS TableName FROM [databasename].INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'viewName'

I set the SQLStatement with an expression since I needed to change the databasename for each iteration. I placed the field TableName in a variable. Before I move on to the next step, where I want to alter the view, I use a Constraint and Expression constraint and make sure that the variable is not empty.

Then I create an Execute SQL task to alter the view. I use the same connection as in the above task and use an expression to create the SQLStatement to something like:

USE [databasename]
GO

ALTER VIEW [dbo].[viewName]
AS
SELECT *
FROM theTable

/Magnus

|||

I had the same problem except I wasn't using SMO Enumeration for the outter loop. You can't just use set the EnumURN property in the Expression Builder to...
"Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

Your missing two parts. You have to set the Expression to...
"RuntimeServer[@.Name='<the name of your SMO Connection in the Connection Manager>']/Server[@.Name='<the server name>']/Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

Hope this at least helps someone down the road. It took me 4 hours to figure it out! Either I'm retarded or it's not explained well.

sql

Finding all views with a specific name, in all databases

Hi,

We are migrating to SQL Server 2005 from 2000 and we have a view created in many of our databases. That view must be changed after the migration because it uses system tables and does not return the correct information in SQL Server 2005.

To do that, I want to create an SSIS-package that loops through all the databases on a particular server. If that view exists in the database, I want to run a script that change the view.

What I try to do:

Set up a For Each Loop container with Foreach SMO Enumerator

Set the connection to my server

Set the Enumerate property to "SMOEnumObj[@.Name='Databases']/SMOEnumType[@.Name='Names']"

On the Variable Mapping page, place Index 0 in Variable User::dbName

In the For Each Loop, place a script task to msgbox the value of User::dbName

This all works good. The problem comes when I try to nest the For Each Loop

Inside the "For Each Database Loop", place a new For Each Loop container with Foreach SMO Enumerator

I connect the Msgbox Script Task with the new For each loop

Use the same connection

Set the EnumURN property in the Expression Builder to "Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

On the Variable Mapping page, place index 0 in Variable User::tabName

In the For Each Loop, place a script task to msgbox the value of User::tabName

When I try to run the package now, it does not at all.

In my Progress tab I see that Validation has started and completed for each task, but I see now errors. Nothing has run.

Test 1: I change the DelayValidation for the Inner Loop to True

Now the package runs, but I never get to the script task in the inner loop.

Question 1: What's the problem?

Question 2: Is there another way to do this?

Regards

Magnus

Have you tried using a second connection? It seems like the above should work for you, but maybe there's an issue with the SMO Enumerator that requires a different connection for the inner loop.|||

Hi Sean, and thanks for your response.

I tried to use a second connection, but I still get the same result.

/Magnus

|||Forget the SMO enumerator in your Foreach loop.

Do an Execute SQL Task and use something like:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME LIKE 'VIEWNAME%'

Then set the resultset to full result set and create an object type var to hold the results. Then loop thru the results using the ForEach ADO.

See this example. I found it very helpful.

http://www.cubido.at/Blog/tabid/176/EntryID/59/Default.aspx|||

Hi Tom, I tried your suggestion and it worked alright. Thanks!

Because I only expect a single row, I used a Single Row instead of Full ResultSet and used a SQL like

SELECT ISNULL(Max(TABLE_NAME), '') AS TableName FROM [databasename].INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'viewName'

I set the SQLStatement with an expression since I needed to change the databasename for each iteration. I placed the field TableName in a variable. Before I move on to the next step, where I want to alter the view, I use a Constraint and Expression constraint and make sure that the variable is not empty.

Then I create an Execute SQL task to alter the view. I use the same connection as in the above task and use an expression to create the SQLStatement to something like:

USE [databasename]
GO

ALTER VIEW [dbo].[viewName]
AS
SELECT *
FROM theTable

/Magnus

|||

I had the same problem except I wasn't using SMO Enumeration for the outter loop. You can't just use set the EnumURN property in the Expression Builder to...
"Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

Your missing two parts. You have to set the Expression to...
"RuntimeServer[@.Name='<the name of your SMO Connection in the Connection Manager>']/Server[@.Name='<the server name>']/Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

Hope this at least helps someone down the road. It took me 4 hours to figure it out! Either I'm retarded or it's not explained well.

Finding all references to a column

I have a task to where I need to move a column from one table to
another. I want to be sure I update any view, stored procedure,
trigger, etc. that references the column. I simply want a query that
will report the related objects and then I will update them manually
but before I go and try and figure out how to do this by querying the
sys tables is there an sp_sproc that will do this?[posted and mailed, please reply in news]

rnewman (newmanr19@.yahoo.com) writes:
> I have a task to where I need to move a column from one table to
> another. I want to be sure I update any view, stored procedure,
> trigger, etc. that references the column. I simply want a query that
> will report the related objects and then I will update them manually
> but before I go and try and figure out how to do this by querying the
> sys tables is there an sp_sproc that will do this?

The best way is to build the database from scripts, with the column
reomved, and then look through all errors you get.

You can also run this query:

select object_name(id)
from sysdepends
where depid = object_id('tbl')
and col_name(depid, depnumber) = 'col'
order by 1

However, this may not be reliable. If you can be confident that all
procedures abd views have been created/altered after the table was
created, this will work. But if you have dropped the table and replaced
with a new version, or you loaded stored procedures before you created
the table, the dependency information will be incomplete.

Note: while the sysdepends tables is documented in Books Online,
the usage of the depnumber as column id is undocumented, and may
be subject to change without notice.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

finding a view or sp

According to a developer that worked on my db, there is a view :
xvw_CheckVoucherReport
I cant see it is there any way i can find out it exsist and if so what database on my server it is in?Enterprise Mangler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_5rg2.asp) would use SQL-DMO (http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_con01_5qup.asp) to show it to you. You could also use SQL Query Analyzer (http://msdn.microsoft.com/library/en-us/qryanlzr/qryanlzr_1zqq.asp) to check the INFORMATION_SCHEMA.VIEWS (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_4pbn.asp) for it.

-PatP|||Select * from Sysobjects should generate all the objects in your database.|||Originally posted by sqlserver2k
Select * from Sysobjects should generate all the objects in your database. Good point, but information_schema views are a lot friendlier and are portable. Why start bad habits (using system tables) when good ones are easier?

-PatP|||I second Pat's proposition ... for not to use SYSTEM TABLES when system supplied SPs and ISVs are provided.

Find Views with NOEXPAND

Hi,
Anyone out there know how to tell whether a view uses the WITH (NOEXPAND)
hint (other than searching the syscomments table for 'NOEXPAND')?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
Another method is to script the view create statements and then search the
scripts for the NOEXPAND keyword.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:ejRg3RwQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Anyone out there know how to tell whether a view uses the WITH (NOEXPAND)
> hint (other than searching the syscomments table for 'NOEXPAND')?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>

Find Views with NOEXPAND

Hi,
Anyone out there know how to tell whether a view uses the WITH (NOEXPAND)
hint (other than searching the syscomments table for 'NOEXPAND')?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgAnother method is to script the view create statements and then search the
scripts for the NOEXPAND keyword.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:ejRg3RwQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Anyone out there know how to tell whether a view uses the WITH (NOEXPAND)
> hint (other than searching the syscomments table for 'NOEXPAND')?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>sql

Find Views with NOEXPAND

Hi,
Anyone out there know how to tell whether a view uses the WITH (NOEXPAND)
hint (other than searching the syscomments table for 'NOEXPAND')?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgAnother method is to script the view create statements and then search the
scripts for the NOEXPAND keyword.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:ejRg3RwQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Anyone out there know how to tell whether a view uses the WITH (NOEXPAND)
> hint (other than searching the syscomments table for 'NOEXPAND')?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>

Find string in Stored Procedures (sp_executesql)

Trying to find a view that is called from many stored procedures - need name
of view only. Query below does not work. Do not want to execute?
declare @.tblname varchar(120),@.sql nvarchar(4000),@.gettext varchar(8000)
declare mycur cursor for
select name from sysobjects where xtype='P'
open mycur
fetch next from mycur into @.tblname
while @.@.fetch_status=0
begin
select @.sql=N'exec sp_helptext '+@.tblname
EXEC sp_executesql
@.stmt = @.sql,
@.params = N'@.gettext int output',
@.gettext= @.gettext output
if charindex( 'ATVINVSTANDARDVIEW',upper(@.gettext))>0
print @.tblname
fetch next from mycur into @.tblname
end
close mycur
deallocate mycur
Regards,
Jamie
You may want to check out the system table syscomments where the source of
all user procedures are held.
Anith
|||http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:96C2F1E4-2672-4DCF-BA4B-EAB8227F44CD@.microsoft.com...
> Trying to find a view that is called from many stored procedures - need
> name
> of view only. Query below does not work. Do not want to execute?
> declare @.tblname varchar(120),@.sql nvarchar(4000),@.gettext varchar(8000)
> declare mycur cursor for
> select name from sysobjects where xtype='P'
> open mycur
> fetch next from mycur into @.tblname
> while @.@.fetch_status=0
> begin
> select @.sql=N'exec sp_helptext '+@.tblname
> EXEC sp_executesql
> @.stmt = @.sql,
> @.params = N'@.gettext int output',
> @.gettext= @.gettext output
> if charindex( 'ATVINVSTANDARDVIEW',upper(@.gettext))>0
> print @.tblname
> fetch next from mycur into @.tblname
> end
> close mycur
> deallocate mycur
>
> --
> Regards,
> Jamie

Find string in Stored Procedures (sp_executesql)

Trying to find a view that is called from many stored procedures - need name
of view only. Query below does not work. Do not want to execute?
---
declare @.tblname varchar(120),@.sql nvarchar(4000),@.gettext varchar(8000)
declare mycur cursor for
select name from sysobjects where xtype='P'
open mycur
fetch next from mycur into @.tblname
while @.@.fetch_status=0
begin
select @.sql=N'exec sp_helptext '+@.tblname
EXEC sp_executesql
@.stmt = @.sql,
@.params = N'@.gettext int output',
@.gettext= @.gettext output
if charindex( 'ATVINVSTANDARDVIEW',upper(@.gettext))>0
print @.tblname
fetch next from mycur into @.tblname
end
close mycur
deallocate mycur
Regards,
JamieYou may want to check out the system table syscomments where the source of
all user procedures are held.
Anith|||http://databases.aspfaq.com/databas...br />
ext.html
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:96C2F1E4-2672-4DCF-BA4B-EAB8227F44CD@.microsoft.com...
> Trying to find a view that is called from many stored procedures - need
> name
> of view only. Query below does not work. Do not want to execute?
> ---
> declare @.tblname varchar(120),@.sql nvarchar(4000),@.gettext varchar(8000)
> declare mycur cursor for
> select name from sysobjects where xtype='P'
> open mycur
> fetch next from mycur into @.tblname
> while @.@.fetch_status=0
> begin
> select @.sql=N'exec sp_helptext '+@.tblname
> EXEC sp_executesql
> @.stmt = @.sql,
> @.params = N'@.gettext int output',
> @.gettext= @.gettext output
> if charindex( 'ATVINVSTANDARDVIEW',upper(@.gettext))>0
> print @.tblname
> fetch next from mycur into @.tblname
> end
> close mycur
> deallocate mycur
>
> --
> Regards,
> Jamie

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 location of data files

What is the tsql cmb to view the file structure location on a database?
This will help me with my attach and detach.sp_help '<dbname>'
sqlteam32 wrote:
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.|||Sorry, make that:
sp_helpdb '<dbname>'
sqlteam32 wrote:
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.|||try the following:
use <dbname>
sp_helpfile
sp_helpgroup
select * from sysfiles
use master
select * from sysaltfiles
"sqlteam32" <mtorpy@.gmail.com> wrote in message
news:1149798455.023852.166600@.h76g2000cwa.googlegroups.com...
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.
>|||Note that following will yield an error. If a call to a stored procedure is
not the first thing in a batch, you must use the keyword EXECUTE (or EXEC)
Also, I'm not sure why you need sp_helpgroup, which returns security groups.
Perhaps you want sp_helpfilegroup?
USE <dbname>
EXEC sp_helpfile
EXEC sp_helpfilegroup
SELECT * FROM sysfiles
--
HTH
Kalen Delaney, SQL Server MVP
"Jayesh Antony Jose" <jayeshaj@.hotmail.com> wrote in message
news:OvXadVDjGHA.3800@.TK2MSFTNGP03.phx.gbl...
> try the following:
> use <dbname>
> sp_helpfile
> sp_helpgroup
> select * from sysfiles
>
> use master
> select * from sysaltfiles
>
> "sqlteam32" <mtorpy@.gmail.com> wrote in message
> news:1149798455.023852.166600@.h76g2000cwa.googlegroups.com...
>> What is the tsql cmb to view the file structure location on a database?
>> This will help me with my attach and detach.
>

Find location of data files

What is the tsql cmb to view the file structure location on a database?
This will help me with my attach and detach.sp_help '<dbname>'
sqlteam32 wrote:
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.|||Sorry, make that:
sp_helpdb '<dbname>'
sqlteam32 wrote:
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.|||try the following:
use <dbname>
sp_helpfile
sp_helpgroup
select * from sysfiles
use master
select * from sysaltfiles
"sqlteam32" <mtorpy@.gmail.com> wrote in message
news:1149798455.023852.166600@.h76g2000cwa.googlegroups.com...
> What is the tsql cmb to view the file structure location on a database?
> This will help me with my attach and detach.
>|||Note that following will yield an error. If a call to a stored procedure is
not the first thing in a batch, you must use the keyword EXECUTE (or EXEC)
Also, I'm not sure why you need sp_helpgroup, which returns security groups.
Perhaps you want sp_helpfilegroup?
USE <dbname>
EXEC sp_helpfile
EXEC sp_helpfilegroup
SELECT * FROM sysfiles
--
HTH
Kalen Delaney, SQL Server MVP
"Jayesh Antony Jose" <jayeshaj@.hotmail.com> wrote in message
news:OvXadVDjGHA.3800@.TK2MSFTNGP03.phx.gbl...
> try the following:
> use <dbname>
> sp_helpfile
> sp_helpgroup
> select * from sysfiles
>
> use master
> select * from sysaltfiles
>
> "sqlteam32" <mtorpy@.gmail.com> wrote in message
> news:1149798455.023852.166600@.h76g2000cwa.googlegroups.com...
>

Find column names used in TSQL and Views

Is there are way to find out which View and Stored Procs contain a column
name or even a string? I am trying to rename a columnin a table from
Cust_name to CustName and want to find what views/stored procs will crash.
Thanks in advance,
Stewart Rogers
DataSort Software, L.C.
How do I find a stored procedure containing <text>?
http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
AMB
"Datasort" wrote:

> Is there are way to find out which View and Stored Procs contain a column
> name or even a string? I am trying to rename a columnin a table from
> Cust_name to CustName and want to find what views/stored procs will crash.
> Thanks in advance,
> --
> Stewart Rogers
> DataSort Software, L.C.
|||looks like AMesa has you covered for sprocs.
This query will get you the views:
use pubs
go
select v.name
from sys.columns c (nolock) inner join sys.views v (nolock) on v.object_id =
c.object_id
where c.name = 'title'
TheSQLGuru
President
Indicium Resources, Inc.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:A3083FBC-6C03-4A28-8B48-F21D8297D3C4@.microsoft.com...[vbcol=seagreen]
> How do I find a stored procedure containing <text>?
> http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
>
> AMB
>
> "Datasort" wrote:

Find column names used in TSQL and Views

Is there are way to find out which View and Stored Procs contain a column
name or even a string? I am trying to rename a columnin a table from
Cust_name to CustName and want to find what views/stored procs will crash.
Thanks in advance,
--
Stewart Rogers
DataSort Software, L.C.How do I find a stored procedure containing <text>?
http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
AMB
"Datasort" wrote:
> Is there are way to find out which View and Stored Procs contain a column
> name or even a string? I am trying to rename a columnin a table from
> Cust_name to CustName and want to find what views/stored procs will crash.
> Thanks in advance,
> --
> Stewart Rogers
> DataSort Software, L.C.|||looks like AMesa has you covered for sprocs.
This query will get you the views:
use pubs
go
select v.name
from sys.columns c (nolock) inner join sys.views v (nolock) on v.object_id =c.object_id
where c.name = 'title'
TheSQLGuru
President
Indicium Resources, Inc.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:A3083FBC-6C03-4A28-8B48-F21D8297D3C4@.microsoft.com...
> How do I find a stored procedure containing <text>?
> http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
>
> AMB
>
> "Datasort" wrote:
>> Is there are way to find out which View and Stored Procs contain a column
>> name or even a string? I am trying to rename a columnin a table from
>> Cust_name to CustName and want to find what views/stored procs will
>> crash.
>> Thanks in advance,
>> --
>> Stewart Rogers
>> DataSort Software, L.C.

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