CODEWITHSUNDEEP
sql-serversql-server-2008
Bob Goblin
Bob Goblin

Reputation: 1269

Query All Views In Database

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

Upvotes: 0

Views: 100

Answers (2)

sam
sam

Reputation: 1304

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.

Upvotes: 0

Spock
Spock

Reputation: 4910

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

Upvotes: 2

Related Questions