• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 512
  • Last Modified:

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?
0
AptDev
Asked:
AptDev
  • 4
  • 3
  • 2
  • +1
1 Solution
 
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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
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
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now