1

I want to find out all the source table name for the columns in a view in SQL server 2014.

I know this question is similar to this but my problem is my view uses tables from multiple databases.

Things that I have tried so far:

SELECT * 
FROM    INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS cu
JOIN    INFORMATION_SCHEMA.COLUMNS AS c
ON      c.TABLE_SCHEMA  = cu.TABLE_SCHEMA
AND     c.TABLE_CATALOG = cu.TABLE_CATALOG
AND     c.TABLE_NAME    = cu.TABLE_NAME
AND     c.COLUMN_NAME   = cu.COLUMN_NAME
WHERE   cu.VIEW_NAME    = '<your view name>'
AND     cu.VIEW_SCHEMA  = '<your view schema>'

This only gives column and table name from current database. I need something similar but from all the databases used in my view.

Feel free to ask questions if I am not clear.

Community
  • 1
  • 1
Abhay Chauhan
  • 404
  • 3
  • 11
  • You'll have to go dynamic - each database has its own schema views, but any particular query can only reference a fixed set of tables/views itself. I'd query whether its worth the work versus just opening the view definition. – Damien_The_Unbeliever Aug 19 '15 at 07:10
  • I have to go through lots of views and fetch column information like data type, table name, is nullable etc. Its a lot of work. I was hoping to create a query that can do the job. – Abhay Chauhan Aug 19 '15 at 08:02
  • @Damien_The_Unbeliever Any pointer on how can I do it dynamically? – Abhay Chauhan Aug 19 '15 at 09:46

1 Answers1

0

have you tried dynamic SQL ? this shoudl resolve your issue also please get back to me on this Weeks that overlap a year SQL

Community
  • 1
  • 1
James Chaggar
  • 15
  • 6