Query All Views In Database

Question

I am looking to return a view name and all record ID's where billingAddress != shippingAddress to further review. I need to query all views in one database. This is my thought process and if their is a better way or faster way to write the query by all means help me out!

What I am stuck on is how to return the view name with the recordID?

Create Table #T (ID Int Identity Not Null, ViewNames VARCHAR(1000)
Create Table #2 (viewNames varchar(1000))
Insert Into #T (ViewNames)
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+']'

---Now this is the piece that I am stuck on as I do not know how to insert the view name into the table as well on each iteration

Declare @N int, @Str nvarchar(2000), @viewname nvarchar(2000), @MaxID int
Set @N = 1
Select @MaxID = Max(ID)
From #T
While (@N<@MaxID)
Begin
  Select @viewname= viewname
  From #T
  Set @Str = ' Insert Into #2(viewname)
  Select Top 1 '''+@viewname+'''
  From '+@viewname+'
  where exists(Select recordID from '+@viewname+' where [shipaddress] != [billaddress] ) '
  Exec sp_eecutesql @str
  Set @N = @N + 1

End Select * from #t

Lookup how to create dynamic SQL. the problem is you can't select from a variable table name unless you use dynamic SQL. Here's an example from a prior post: http://stackoverflow.com/questions/1325044/dynamic-sql-passing-table-name-as-parameter — xQbert, Jan 16 '15 at 20:16
@xQbert - Syntactically my code above works for returning recordID's. I just need a way of returning the viewname that recordID is associated with. — Bob Goblin, Jan 16 '15 at 20:18
@king.code - let me restate. The 1st statement executes error free. The 2nd set that I am unsure of does not, was me attempting to show code of the output I was needing. Still working on it tho. — Bob Goblin, Jan 16 '15 at 20:54

score 2 · Accepted Answer · answered Jan 16 '15 at 21:37

Try changing your dynamic query like this.

You said you wanted the view name, and record id, so you need to add a column to #2

SET @Str = 'INSERT INTO #2(viewname, recordid)
SELECT ''' + quotename(@viewname) + ''', recordID 
FROM '+ quotename(@viewname) + '
WHERE [shipaddress] != [billaddress]'

EXEC sp_executesql @str

Unless you're sure of the object names, you should try and use quotename when building up dynamic SQL

You do have a problem in your logic though...

You are missing a where clause in the query that assigns the value to @viewname

Try this...

SELECT @viewname= viewname
FROM #T
WHERE ID = @N

score 0 · Answer 2 · answered Jan 23 '15 at 05:15

0

I do not understand sql returns set of rows so your variable @viewname can not be assigned value row by row. By default your @viewname will be assigned last row of table T.

answered Jan 23 '15 at 05:15

sam

1,242
3
12
31

Query All Views In Database

2 Answers2