AptDev
asked on
Text Search of SQL Views definitions
I am trying to learn a new third party database layout. I have over 700 sample views. I would like to be able to search through those views for, say a table name, or maybe two table names, to find out how the table links are being used, and what fields are being renamed in the views, what tables are being used the most, and for what views, etc.
Is there an easy way to do a text search of View Definitions in SQL Server 2005?
Is there an easy way to do a text search of View Definitions in SQL Server 2005?
exec sp_views_rowset <viewname>
ASKER
Malik1947,
Unfortunately, that command requires me to put in an individual view name. Since I have over 700, I want to search "all views" for a word, phrase, or term. Not each individual view.
Any other ideas?
Unfortunately, that command requires me to put in an individual view name. Since I have over 700, I want to search "all views" for a word, phrase, or term. Not each individual view.
Any other ideas?
here
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This should take care of all of the objects reference by views. You can change the system view name as needed for different objects. This is for 2005 only, as it uses recursion.
BEGIN
DECLARE @ObjectName AS VARCHAR(200)--holds the name of the object for which we find dependancies
DECLARE @DependantName AS VARCHAR(200)--holds the dependant object name
DECLARE @DepObjectType AS VARCHAR(2)
DECLARE @ObjectID AS BIGINT
DECLARE @Result TABLE
(
ObjectName VARCHAR(200),
DepObjectName VARCHAR(200),
DepObjectType VARCHAR(2)
)--temp table to store results passed back
SET NOCOUNT ON ;
--Cursor used to hold the values of querying the sysobjects table with the matching criteria passed in
DECLARE object_cursor CURSOR FAST_FORWARD
FOR SELECT [name]
FROM sys.views
OPEN object_cursor
FETCH NEXT FROM object_cursor INTO @ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
WITH DependentObjectCTE ( objectid, objectname, dependentobjname, dependentobjid, deptype )
AS ( SELECT DISTINCT
sd.id,
OBJECT_NAME(sd.id),
OBJECT_NAME(depid),
depid,
xtype
FROM sysdepends sd ( NOLOCK )
JOIN sysobjects so ON sd.depid = so.[id]
WHERE sd.id = OBJECT_ID(@ObjectName)
UNION ALL--required for a recursive CTE
SELECT sd.id,
OBJECT_NAME(sd.[id]),
OBJECT_NAME(depid),
depid,
do.deptype
FROM sysdepends sd ( NOLOCK )
JOIN sysobjects so ON sd.id = so.[id]
JOIN DependentObjectCTE do ON sd.[id] = do.dependentobjid
WHERE xtype IN ( 'P', 'V', 'FN' )--stored procedure, view, function
)
INSERT INTO @Result
SELECT DISTINCT
objectname,
dependentobjname,
deptype
FROM DependentObjectCTE
ORDER BY objectname
OPTION ( MAXRECURSION 10000 ) ;
FETCH NEXT FROM object_cursor INTO @ObjectName
END
CLOSE object_cursor
DEALLOCATE object_cursor
SELECT DISTINCT
ObjectName AS 'PrimaryObject',
DepObjectName AS 'DependantObject'
FROM @Result
ORDER BY ObjectName,
DepObjectName
END
BEGIN
DECLARE @ObjectName AS VARCHAR(200)--holds the name of the object for which we find dependancies
DECLARE @DependantName AS VARCHAR(200)--holds the dependant object name
DECLARE @DepObjectType AS VARCHAR(2)
DECLARE @ObjectID AS BIGINT
DECLARE @Result TABLE
(
ObjectName VARCHAR(200),
DepObjectName VARCHAR(200),
DepObjectType VARCHAR(2)
)--temp table to store results passed back
SET NOCOUNT ON ;
--Cursor used to hold the values of querying the sysobjects table with the matching criteria passed in
DECLARE object_cursor CURSOR FAST_FORWARD
FOR SELECT [name]
FROM sys.views
OPEN object_cursor
FETCH NEXT FROM object_cursor INTO @ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
WITH DependentObjectCTE ( objectid, objectname, dependentobjname, dependentobjid, deptype )
AS ( SELECT DISTINCT
sd.id,
OBJECT_NAME(sd.id),
OBJECT_NAME(depid),
depid,
xtype
FROM sysdepends sd ( NOLOCK )
JOIN sysobjects so ON sd.depid = so.[id]
WHERE sd.id = OBJECT_ID(@ObjectName)
UNION ALL--required for a recursive CTE
SELECT sd.id,
OBJECT_NAME(sd.[id]),
OBJECT_NAME(depid),
depid,
do.deptype
FROM sysdepends sd ( NOLOCK )
JOIN sysobjects so ON sd.id = so.[id]
JOIN DependentObjectCTE do ON sd.[id] = do.dependentobjid
WHERE xtype IN ( 'P', 'V', 'FN' )--stored procedure, view, function
)
INSERT INTO @Result
SELECT DISTINCT
objectname,
dependentobjname,
deptype
FROM DependentObjectCTE
ORDER BY objectname
OPTION ( MAXRECURSION 10000 ) ;
FETCH NEXT FROM object_cursor INTO @ObjectName
END
CLOSE object_cursor
DEALLOCATE object_cursor
SELECT DISTINCT
ObjectName AS 'PrimaryObject',
DepObjectName AS 'DependantObject'
FROM @Result
ORDER BY ObjectName,
DepObjectName
END
champ and Snarf
there is no need to get into that much detail to get what he's trying to do.
he just needs a table list.
and that is stored under the information system table.
there is no need to get into that much detail to get what he's trying to do.
he just needs a table list.
and that is stored under the information system table.
ASKER
Snarf0001,
That almost does what I need... it shows the text of views, but it's only showing the system's views, not the views of my individual databases. I''m working with it to see if I can figure out how to get it to look further...
That almost does what I need... it shows the text of views, but it's only showing the system's views, not the views of my individual databases. I''m working with it to see if I can figure out how to get it to look further...
ASKER
Snarf0001,
Ok, I've almost got it. This is working great! It's exactly what I needed. Here's what's happening...
(I'm new to SQL 2005, so pardon my lack of proper terminology)
If I just open a new database query, the query screen tab says: SERVER1.MASTER... and when I run your query, I only search system views.
If I go into one of my database, and open a query, the query screen tab says: SERVER1.DB1... and I can search all of those views just fine!
I have about 15 databases, so my question is: is there a way to use your same query, to search all views in all databases, by opening a different kind of a tab (somehow backing out a layer, or something?)
Ok, I've almost got it. This is working great! It's exactly what I needed. Here's what's happening...
(I'm new to SQL 2005, so pardon my lack of proper terminology)
If I just open a new database query, the query screen tab says: SERVER1.MASTER... and when I run your query, I only search system views.
If I go into one of my database, and open a query, the query screen tab says: SERVER1.DB1... and I can search all of those views just fine!
I have about 15 databases, so my question is: is there a way to use your same query, to search all views in all databases, by opening a different kind of a tab (somehow backing out a layer, or something?)
That is a limitation, the command needs to be called on each invididual database, but it should return both system and user views on each one.
Malik1947: The request in his second post specifically says to search for words or phrases as well, not just simple table names
Malik1947: The request in his second post specifically says to search for words or phrases as well, not just simple table names
ASKER
Excellent solution! Thank you!