Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Run 1 Query against All views

    In SQL server 2008 how can I run the same query against each view in that database?

    One database about 75 views.

    Or even something like
    Select 'yes' from view
    Where shipping_status = 'delayed'

    And return a list of view names so I know which views to query against.


    Edit ------
    I ran
    Code:
    select name from sysobjects where type = v
    and got a list of my views. How can I now run my SQL statement against the list to see which views contain the info?
    Last edited by jo15765; 07-17-14 at 21:59. Reason: Code Tags Updated

  2. #2
    Join Date
    Feb 2012
    Posts
    188
    I ended up using the below to get a list of all the vies in the database - then just run a big union all with each of the view names listed:
    Code:
    CREATE TABLE #T (ID INT IDENTITY NOT NULL, TableNames VARCHAR(1000))
    INSERT INTO #T (TableNames)
    SELECT '['+C.TABLE_CATALOG+'].['+C.TABLE_SCHEMA+'].['+C.TABLE_NAME+']' TableName
    FROM INFORMATION_SCHEMA.COLUMNS C
    JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_CATALOG = T.TABLE_CATALOG	AND
    C.TABLE_SCHEMA = T.TABLE_SCHEMA AND
    C.TABLE_NAME = T.TABLE_NAME
    WHERE T.TABLE_TYPE = 'View'
    GROUP BY '['+C.TABLE_CATALOG+'].['+C.TABLE_SCHEMA+'].['+C.TABLE_NAME+']'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •