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?
AptDevAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Malik1947Commented:
exec sp_views_rowset <viewname>
0
AptDevAuthor Commented:
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?
0
Malik1947Commented:
here
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Snarf0001Commented:
If you need to search for specific columns / phrases, you can also use sysobjects and syscomments:

select distinct name
from sysobjects o
join syscomments c on o.id = c.id
where c.text like '%phrase i want to find%'
and xtype = 'V'


the xtype part limits the results to views, if you don't specify it will also scan stored proc and function definitions.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chapmandewCommented:
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





0
Malik1947Commented:
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.
0
AptDevAuthor Commented:
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...
0
AptDevAuthor Commented:
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?)
0
Snarf0001Commented:
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
0
AptDevAuthor Commented:
Excellent solution!  Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.