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