Menu
How can i get to know the tables used in a view in SQL Server? Is there a script or a tool that could let me know the tables used in a view and can also list down the fields?
Hope this clears the question. Let me know if not.
Please guide! Thanks!
382
You find the details in dictionary tablesSELECT * FROM dba_viewsWHERE view_name = <view name >TEXT column contains the actual query which used while creating views and this will give details what are all tables used.
SELECT name as Foreign_Key ,schema_name(schema_id) as Schema_Name ,object_name(parent_object_id) as Table_Name FROM sys. foreign_keys WHERE Referenced_object_id = object_id('dbo. user','U'); if your table belongs to a different schema other than dbo then replace the schema name.
In Object Explorer, expand the database that contains the view to which you want to view the properties, and then expand the Views folder. Right-click the view of which you want to view the properties and select View Dependencies. Select Objects that depend on [view name] to display the objects that refer to the view.
You can use the OBJECTPROPERTY() function in SQL Server to check whether or not a table is referenced by a foreign key. To do this, pass the table's object ID as the first argument, and TableHasForeignRef as the second argument.
select
cols.*
from
sys.sql_expression_dependencies objs
outer apply sys.dm_sql_referenced_entities ( OBJECT_SCHEMA_NAME(objs.referencing_id) + N'.' + object_name(objs.referencing_id), N'OBJECT' ) as cols
where
objs.referencing_id = object_id('view_name_here')
Reference: sys.dm_sql_referenced_entities (Transact-SQL) .
185
The simplest way to see the content of (most) objects would be:
sp_helptext blah
Where you substitute blah with the name of the object. This would yield the actual code which created the object. in this case, for instance it could result in:
CREATE VIEW blah
AS
select blah.column1,blah.column2 from blah_table
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
