CODEWITHSUNDEEP
sql-serversql-server-2000
Bob Goblin
Bob Goblin

Reputation: 1269

List Of Views Not Containing Certain Field

I know the syntax to get a list of all views in a database, but how could I then flip that to show me a list of all the views that do not contain a field? For example, I want to see all views that do not contain the field Salutation, how would I do such? This would be the syntax I used to get a list of all views in a database:

SET NOCOUNT ON;

CREATE TABLE T
  (
     ID INT IDENTITY NOT NULL,
     VN VARCHAR(1000)
  );

INSERT INTO T
            (VN)
SELECT '[' + C.Table_Catalog + '].[' + C.Table_Schema + '].[' + C.Table_Name + ']' VN
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 + ']';

Upvotes: 1

Views: 41

Answers (1)

Xedni
Xedni

Reputation: 4695

SELECT QUOTENAME(v.TABLE_CATALOG) + '.'
       + QUOTENAME(v.TABLE_SCHEMA) + '.'
       + QUOTENAME(v.TABLE_NAME)
FROM   INFORMATION_SCHEMA.TABLES v
WHERE  NOT EXISTS (SELECT 1
                   FROM   INFORMATION_SCHEMA.COLUMNS ic
                   WHERE  ic.TABLE_NAME = v.TABLE_NAME
                          AND ic.TABLE_SCHEMA = v.TABLE_SCHEMA
                          AND ic.COLUMN_NAME = 'Salutation')
       AND v.TABLE_TYPE = 'VIEW'

Upvotes: 3

Related Questions