August 15, 2012 at 1:19 pm
Is there a way to find all instances where a column in an underlying table appears in a view?
For example, if I have this table and this view and want to research the use of [IsLive]. How would I find the view [_test].
CREATE TABLE [dbo].[ShowHeaders](
[ShowNumber] [varchar](8) NOT NULL,
[ShowType] [char](3) NOT NULL,
[NameLong] [varchar](150) NOT NULL,
[NameShort] [varchar](150) NOT NULL,
[Status] [char](3) NULL,
[IsLive] [bit] NOT NULL
GO
CREATE VIEW [dbo].[_test]
AS
SELECT ShowNumber, IsLive
FROM dbo.ShowHeaders
GO
August 15, 2012 at 1:28 pm
there sure is:
this example will give you all columns that have a certain name;
if you want to specifically limit it to a specific table also, simply uncomment out the table name part, and of course change it to teh right table.
SELECT
depz.referenced_schema_name,
depz.referenced_entity_name,
objz.type_desc,
colz.name AS ColumnName
FROM sys.sql_expression_dependencies depz
INNER JOIN sys.objects objz ON depz.referenced_id=objz.object_id
LEFT OUTER JOIN sys.columns colz ON objz.object_id = colz.object_id
AND colz.column_id = referencing_minor_id
WHERE 1=1
--AND referencing_id = OBJECT_ID(N'MyTable');
AND colz.name = 'EmployeeNumber'
Lowell
August 15, 2012 at 1:29 pm
I use this, but syscomments is not the best place anymore. I'm sure someone will respond with the new dmv for this.
SELECT *
FROM sys.objects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.object_Id = SC.ID
AND SO.Type = 'V'
AND SC.Text LIKE '%column_name%'
ORDER BY SO.Name
Jared
CE - Microsoft
August 15, 2012 at 1:29 pm
Lowell (8/15/2012)
there sure is:this example will give you all columns that have a certain name;
if you want to specifically limit it to a specific table also, simply uncomment out the table name part, and of course change it to teh right table.
SELECT
depz.referenced_schema_name,
depz.referenced_entity_name,
objz.type_desc,
colz.name AS ColumnName
FROM sys.sql_expression_dependencies depz
INNER JOIN sys.objects objz ON depz.referenced_id=objz.object_id
LEFT OUTER JOIN sys.columns colz ON objz.object_id = colz.object_id
AND colz.column_id = referencing_minor_id
WHERE 1=1
--AND referencing_id = OBJECT_ID(N'MyTable');
AND colz.name = 'EmployeeNumber'
+1 Thanks Lowell!
Jared
CE - Microsoft
August 15, 2012 at 1:31 pm
ok hang on, i believe that code returns procedure or function dependancies...testing it now;
i have a couple of otehr examples too.
Lowell
August 15, 2012 at 1:36 pm
SELECT
OBJECT_NAME(depz.referencing_id), *
FROM sys.sql_expression_dependencies depz
INNER JOIN sys.objects objz ON depz.referenced_id=objz.object_id
LEFT OUTER JOIN sys.columns colz ON objz.object_id = colz.object_id
AND colz.column_id = referencing_minor_id
WHERE 1 = 1
AND referenced_id = OBJECT_ID(N'MyTable')
this definitely gets me the TABLES after testing...still working on columns again... i KNOW i solved this before.
Lowell
August 15, 2012 at 1:37 pm
I ran it as is and didn't get any results. I then ran it leaving only the WHERE 1=1 which returned pretty much all the tables, but ColumnName is showing as NULL. When I tried WHERE 1=1 and colz.name = 'ShowNumber', there were no results. However, 1=1 returned views that I know 'ShowNumber' is a part of.
SELECT
depz.referenced_schema_name,
depz.referenced_entity_name,
objz.type_desc,
colz.name AS ColumnName
FROM sys.sql_expression_dependencies depz
INNER JOIN sys.objects objz ON depz.referenced_id=objz.object_id
LEFT OUTER JOIN sys.columns colz ON objz.object_id = colz.object_id
AND colz.column_id = referencing_minor_id
WHERE 1=1 ORDER BY type_desc
August 15, 2012 at 1:43 pm
ok the query i gave works for objects created WITH SCHEMABINDING... in that case, the referencing_minor_id is not zero, which is how i get some results for column names on some of my views with columns, but not all columns;
i am sure i solved this before, but my script search isn't finding it yet.
Lowell
August 15, 2012 at 1:49 pm
What about this? I think without schema_binding on the view, you have to search the view definition...
USE databaseName
GO
SELECT Distinct SO.Name
FROM sys.sysobjects SO (NOLOCK)
INNER JOIN sys.syscomments SC (NOLOCK) on SO.Id = SC.id
AND SO.Type = 'V'--P for stored procs
AND SC.Text LIKE '%textToFind%'
ORDER BY SO.Name
Jared
CE - Microsoft
August 15, 2012 at 1:52 pm
well this uses soon to be deleted oldstyle syobjects, sysdepends,etc, but it works correctly;
i'll look into updating it to use new metadata instead.
SELECT
OBJECT_NAME(sd.id) Referencing_Object,
(SELECT
xtype
FROM sysobjects so
WHERE so.id = sd.id) Object_Type
FROM sysobjects so
INNER JOIN syscolumns sc
ON so.id = sc.id
INNER JOIN sysdepends sd
ON so.id = sd.depid
AND sc.colid = sd.depnumber
WHERE so.id = OBJECT_ID('GMACT')
AND sc.name = 'ACTTBLKEY'
Lowell
August 15, 2012 at 1:56 pm
And this?
SELECT DISTINCT so.name
FROM sys.objects SO (NOLOCK)
INNER JOIN sys.sql_modules SC (NOLOCK) on SO.object_Id = SC.object_id
AND SO.Type = 'V'--P for stored procs
AND SC.definition LIKE '%textSearch%'
ORDER BY SO.Name
Jared
CE - Microsoft
August 15, 2012 at 2:07 pm
SQLKnowItAll (8/15/2012)
And this?
SELECT DISTINCT so.nameFROM sys.objects SO (NOLOCK)
INNER JOIN sys.sql_modules SC (NOLOCK) on SO.object_Id = SC.object_id
AND SO.Type = 'V'--P for stored procs
AND SC.definition LIKE '%textSearch%'
ORDER BY SO.Name
That's looking good.
Now, can you go back in time to my younger self 3 years ago and use a crowbar to beat some sense into him so that he's thinking in as broad terms as older me is now?
When I created one of the primary tables for the DB that I'm working on, I made several assumptions about how some data would be stored, used and worked with. Now that I'm actually to the point of working with those columns, they're not what I need.
Younger self, I hate you.
August 15, 2012 at 2:16 pm
SQLKnowItAll (8/15/2012)
And this?
SELECT DISTINCT so.nameFROM sys.objects SO (NOLOCK)
INNER JOIN sys.sql_modules SC (NOLOCK) on SO.object_Id = SC.object_id
AND SO.Type = 'V'--P for stored procs
AND SC.definition LIKE '%textSearch%'
ORDER BY SO.Name
If I remember correctly, the system view sys.views only lists user views, and you might be able to replace sys.objects above with sys.views. That would also eliminate the the test SO.Type = 'V'.
August 15, 2012 at 2:28 pm
I would cheat...and download SQL Search from Redgate and let that utility do the searching for me. :w00t:
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 15, 2012 at 2:37 pm
Jeffrey Williams 3188 (8/15/2012)
I would cheat...and download SQL Search from Redgate and let that utility do the searching for me. :w00t:
Dear Younger Self 10 minutes ago, Don't do what you just did and wait for this post. (I sorta forgot that I only queried the views, not any stored procedures or triggers. Not too bad of a disaster.)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply