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

find SP dependencies

SQL 2K
My db has a number of SP that are no longer in use. sp_depends (and Show
Dependencies from EM) sees hit and miss. Both tell me that usp_Begin has no
dependencies even though it contains this
ALTER PROCEDURE uspCreatePayerServiceDataFromTp3Data
(
@.CredID AS BIGINT,
@.BillerId AS BIGINT,
@.ErrorNumber AS INT OUTPUT,
@.Description AS VARCHAR(4000) OUTPUT)
AS
SET @.Description = ISNULL(@.Description,'')
EXEC @.ErrorNumber = /*SQLTRCLP*/TP3_TP4_Migration.dbo.usp_SelectAccountUsers
@.CredID, @.BillerId, @.Description OUTPUT
SET @.ErrorNumber = @.@.ERROR
RETURN @.ErrorNumber
and it only finds one of the three SPs called in
TP3_TP4_Migration.dbo.usp_SelectAccountUsers but all of the tables.
Is there a reliable way to determine what SP's truly depend on what othe
objects?
SQL Server is able to do this, becuase it barks if I misspell an object name
.
thanks
kevinOJ did a script.. as sp_depends has issues...
create procedure usp_findreferences
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
drop proc usp_findreferences
HTH. Ryan
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:6D68AE72-0B0C-434F-83EC-FBBF125A46F9@.microsoft.com...
> SQL 2K
> My db has a number of SP that are no longer in use. sp_depends (and Show
> Dependencies from EM) sees hit and miss. Both tell me that usp_Begin has
> no
> dependencies even though it contains this
> ALTER PROCEDURE uspCreatePayerServiceDataFromTp3Data
> (
> @.CredID AS BIGINT,
> @.BillerId AS BIGINT,
> @.ErrorNumber AS INT OUTPUT,
> @.Description AS VARCHAR(4000) OUTPUT)
> AS
> SET @.Description = ISNULL(@.Description,'')
> EXEC @.ErrorNumber =
> /*SQLTRCLP*/TP3_TP4_Migration.dbo.usp_SelectAccountUsers
> @.CredID, @.BillerId, @.Description OUTPUT
> SET @.ErrorNumber = @.@.ERROR
> RETURN @.ErrorNumber
> and it only finds one of the three SPs called in
> TP3_TP4_Migration.dbo.usp_SelectAccountUsers but all of the tables.
> Is there a reliable way to determine what SP's truly depend on what othe
> objects?
> SQL Server is able to do this, becuase it barks if I misspell an object
> name.
> thanks
> kevin