column dependency search

I posted this yesterday with no luck. I thought Id repost but be a bit more
descriptive. I want to be able to figure out where a column is explicitly
being called.
create proc myproc
as
select MyColumn from Table1
create view myview
as
select * from Table1
In the above scenario, I could write a query to search for MyColumn.
Therefore, MyProc would be returned in the results, but not MyView as it
didn't specifically name the MyColumn column. Is there a way?
TIA,
ChrisR
try using sp_depends [object name]
"ChrisR" wrote:

> I posted this yesterday with no luck. I thought Id repost but be a bit more
> descriptive. I want to be able to figure out where a column is explicitly
> being called.
> create proc myproc
> as
> select MyColumn from Table1
> create view myview
> as
> select * from Table1
> In the above scenario, I could write a query to search for MyColumn.
> Therefore, MyProc would be returned in the results, but not MyView as it
> didn't specifically name the MyColumn column. Is there a way?
> --
> TIA,
> ChrisR
|||You would have to add logic to look for tables with an * in the select list.
I am not sure how to do that but just another of many good reasons whey you
should never use * in production code.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:4B53BD66-CA89-4C7D-AF7D-CC9D55D6969F@.microsoft.com...
>I posted this yesterday with no luck. I thought Id repost but be a bit more
> descriptive. I want to be able to figure out where a column is explicitly
> being called.
> create proc myproc
> as
> select MyColumn from Table1
> create view myview
> as
> select * from Table1
> In the above scenario, I could write a query to search for MyColumn.
> Therefore, MyProc would be returned in the results, but not MyView as it
> didn't specifically name the MyColumn column. Is there a way?
> --
> TIA,
> ChrisR
|||sp_depends is for a whole table, not a column.
TIA,
ChrisR
"FredG" wrote:
[vbcol=seagreen]
> try using sp_depends [object name]
> "ChrisR" wrote:
|||I want to ignore tables with an *.
TIA,
ChrisR
"Andrew J. Kelly" wrote:

> You would have to add logic to look for tables with an * in the select list.
> I am not sure how to do that but just another of many good reasons whey you
> should never use * in production code.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:4B53BD66-CA89-4C7D-AF7D-CC9D55D6969F@.microsoft.com...
>
>
|||Chris,
You have me confused then as to what you are asking. Are you looking for
sample code for the query to execute to find the column? If so then how
about this:
CREATE PROCEDURE find_text
@.Search VARCHAR(200)
-- Search the stored procedures and Scheduled Jobs for the text string
passed
-- and show which objects it is found.
AS
SET NOCOUNT ON
SET @.Search = '%' + @.Search + '%'
-- Create a table var to store each part
DECLARE @.tmpSearch TABLE ([Object Name] VARCHAR(50),[Sub Level]
VARCHAR(40),[Object Type] VARCHAR(24))
INSERT INTO @.tmpSearch
SELECT DISTINCT a.Name,SPACE(40) AS 'Sub Level',
CASE WHEN a.xType = 'C' THEN 'Check Constraint'
WHEN a.xType = 'D' THEN 'Default constraint'
WHEN a.xType = 'F' THEN 'FK constraint'
WHEN a.xType = 'L' THEN 'Log'
WHEN a.xType = 'FN' THEN 'Scaler Function'
WHEN a.xType = 'IF' THEN 'Inline Function'
WHEN a.xType = 'P' THEN 'Stored Procedure'
WHEN a.xType = 'PK' THEN 'PK constraint'
WHEN a.xType = 'RF' THEN 'Replication Filer'
WHEN a.xType = 'S' THEN 'System Table'
WHEN a.xType = 'TF' THEN 'Table Function'
WHEN a.xType = 'TR' THEN 'Trigger'
WHEN a.xType = 'U' THEN 'User Table'
WHEN a.xType = 'UQ' THEN 'Unique constraint'
WHEN a.xType = 'V' THEN 'View'
WHEN a.xType = 'X' THEN 'Extended SP'
ELSE 'Other' END AS Object_Type
FROM dbo.sysObjects AS a WITH (NOLOCK)
INNER JOIN dbo.syscomments AS b WITH (NOLOCK)
ON a.ID = b.ID
WHERE ENCRYPTED = 0
AND Text LIKE @.Search
INSERT INTO @.tmpSearch
SELECT A.[Name],B.Step_Name,'Scheduled Job' AS Object_Type
FROM MSDB.dbo.SYSJOBS AS A WITH (NOLOCK) INNER JOIN
MSDB.dbo.SYSJOBSTEPS AS B WITH (NOLOCK)
ON A.JOB_ID = B.JOB_ID
WHERE B.Command LIKE @.Search
SELECT [Object Name],[Object Type],[Sub Level]
FROM @.tmpSearch
ORDER BY [Object Type],[Object Name]
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:9744BE53-07DF-4427-96F5-D0C10EFBD388@.microsoft.com...[vbcol=seagreen]
>I want to ignore tables with an *.
> --
> TIA,
> ChrisR
>
> "Andrew J. Kelly" wrote:
|||You can search syscomments.
Josh
declare @.keyword varchar(32)
set @.keyword = 'MyColumn'
select
o.[id] as sid,
o.[name] as procname,
c.colid,
cast(c.[text] as varchar(4000)) as proctext
into #mythingy
from sysobjects o
inner join syscomments c
on o.id=c.id
where xtype in ('p','if')
and left(o.[name],3) not in ('dmf','dt_')
and charindex(@.keyword ,c.[text])>1
order by o.[name], c.colid
select * from #mythingy
-- and then, for a few pennies more, you can parse the lines:
set nocount on
declare @.jx int, @.cmd varchar(255), @.t char(1), @.lf char(1), @.lx int
declare @.sid int, @.procname varchar(255), @.colid int, @.proctext
varchar(4000)
declare @.keyword varchar(32)
set @.keyword = 'MyColumn'
declare @.myhits table
(
sidint,
procnamevarchar(255),
colidint,
jxint,
cmdvarchar(4000)
)
declare ix cursor for
select sid, procname, colid, proctext from #mythingy
set @.t = char(9)
set @.lf = char(10)
OPEN ix
FETCH NEXT FROM ix into @.sid, @.procname, @.colid, @.proctext
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.jx = charindex(@.keyword,@.proctext)
while @.jx > 0
begin
set @.cmd = substring(@.proctext,@.jx,60)
set @.lx = charindex(@.lf,@.cmd)
if @.lx > 0
set @.cmd = left(@.cmd,@.lx-1)
set @.cmd = replace(@.cmd,@.t,' ')
print str(@.sid) + ' ' + @.procname + ' ' + str(@.colid) + ' ' +
str(@.jx) + ' ' + @.cmd
insert into @.myhits
values (@.sid, @.procname, @.colid, @.jx, @.cmd)
set @.jx = @.jx + 8
set @.jx = charindex(@.keyword,@.proctext, @.jx)
end
FETCH NEXT FROM ix into @.sid, @.procname, @.colid, @.proctext
print ''
END
CLOSE ix
DEALLOCATE ix
select *
from @.myhits
order by cmd, procname, colid, jx
On Wed, 21 Dec 2005 07:45:03 -0800, ChrisR
<ChrisR@.discussions.microsoft.com> wrote:
>I posted this yesterday with no luck. I thought Id repost but be a bit more
>descriptive. I want to be able to figure out where a column is explicitly
>being called.
>create proc myproc
>as
>select MyColumn from Table1
>create view myview
>as
>select * from Table1
>In the above scenario, I could write a query to search for MyColumn.
>Therefore, MyProc would be returned in the results, but not MyView as it
>didn't specifically name the MyColumn column. Is there a way?