January 23, 2018 at 3:21 am
I am looking in the information schema.
I am trying to find the connection between the table column and the view column.
In the view the name and the order are different from the name and order in the table, how do I match the two ?
Thanks for your time and attention,
Ben Brugman
January 23, 2018 at 3:38 pm
Hi Ben, what are you trying to match up, exactly? You're trying to find out if a table is used within a view? You are saying the name and order is not the same in the view, so not sure how it would match from information_schema at all, but maybe if I understood what you're trying to do it would help.
Thanks,
Jon
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
January 23, 2018 at 10:42 pm
ben.brugman - Tuesday, January 23, 2018 3:21 AMI am looking in the information schema.I am trying to find the connection between the table column and the view column.
In the view the name and the order are different from the name and order in the table, how do I match the two ?
Thanks for your time and attention,
Ben Brugman
Yes, Could you please explain a bit ? Did you mean to say that you'd wanted a common key b/w Information schema tables and views ??
January 25, 2018 at 11:18 am
Hallo Jonathan and Subramaniam,
I am monitoring what the effect of a table insert/update/delete has on a View.
From the view definition it is possible to determine which fields from the table part of the view. Also from the view definition you can see which fields will be influenced by changes.
But from the information schema tables I can not determine which fields of the table map to which fields in the view.
Analyzing the view by eye is possible, but I want to automate this. So I was hoping that I can find this information in the schema or system tables. Or that someone in this group has tackled the same or similar problem.
ben
January 25, 2018 at 12:14 pm
information_schema doesn't do that, you are correct that you would have to parse the view definition to see what tables and fields are affected. That is the purpose of a view, to obfuscate those details for either simplicity or security.
*edit: to expound a little further, I always tell folks to consider a view like a punch-card, a piece of paper with a hole in it. There's nothing actually contained in the view, but it restricts what you can see. Changes to data don't actually change the view in any way, but you can see it by viewing the underlying tables through that "hole".
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
January 25, 2018 at 2:09 pm
maybe sys.dm_sql_referenced_entities or sys.dm_sql_referencing_entities can help?
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-sql-referenced-entities-transact-sql
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-sql-referencing-entities-transact-sql
here's an example for using referenced entities:SELECT v.name AS view_name, referenced_database_name, referenced_schema_name, referenced_entity_name, referenced_minor_name
FROM sys.views v
CROSS APPLY sys.dm_sql_referenced_entities (SCHEMA_NAME(v.schema_id) + N'.' + OBJECT_NAME(v.object_id), 'OBJECT')
WHERE referenced_minor_id > 0
ORDER BY referenced_database_name, referenced_schema_name, referenced_entity_name, referenced_minor_id
January 25, 2018 at 4:52 pm
well that's interesting, Chris, thanks!
However, it only really works when it's within the same database, when I tried it on a view looking into other databases it gave me tables, but not columns (had to comment out the referenced_minor_id > 0 part to even see that, as >0 means columns per the documentation you linked)
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
January 26, 2018 at 8:39 am
First of all, everybody thanks for participating.
I came up with the next 2 sets of code:
The first set is more compact and can handle longer views.
The second set does not require specific functions, but can only handle views of approximately 8000 characters. (uses Master.dbo.DelimitedSplit8K)
From this code I might be building more functionality, like 'referencing' the fields with the fieldnames in the Views.
It took some effort to build this function, so it would be nice if this get's used by others :-).
Please comment, or reply with suggestions or improvements.
Getting the data from sys.views can give some errors, which I see as warnings.
To use the code, use the second version, replace the '<Name_of_A_View>' and '%<Name_of_a_field_or_part_of_field>%' with your view and fieldnames.
The code builds three temporary tables which are removed at the end of the script. (keep the results for further inspection).
The code does not make other alterations to the database. (Selects only).
Suggestions, improvements welcome. Also if this is found usefull I'll be happy to hear that.
Greetings,
Ben Brugman
--
-- Ben Brugman
-- 20180126
--
-- This function will give some insigt of usage of fields within a View.
-- This gives help with referenced_minor_name fields and 'mayor_field_names'
--
declare @NL varchar(30) = char(13)+char(10), @tab varchar(30) = char(9)
;With
S as (SELECT v.name AS S_view_name, referenced_database_name, referenced_schema_name, referenced_entity_name, referenced_minor_name
FROM sys.views v CROSS APPLY sys.dm_sql_referenced_entities (SCHEMA_NAME(v.schema_id) + N'.' + OBJECT_NAME(v.object_id), 'OBJECT') )
,V1 as (select name AS V_view_name, OBJECT_DEFINITION(o.object_id) as string FROM sys.objects o WHERE o.type IN ('V')) -- and name like 'STEF_BIL_Factuurregels_CDR')
,V2 AS (SELECT V_view_name, master.dbo.replace3(string,char(13)+char(10)+'|'+char(9)+'|, --|,--|--,|-- ,|,|<komma>| FROM | JOIN | on | SELECT | WHEN | AND |sarbnetfets', ' | |@<komma> --|@<komma> --|@-- <komma>|@-- <komma>|<komma>@|<komma>|@<FROM> |@<JOIN> |@<ON> |@<SELECT> |@<WHEN> |@<AND> |NoReplace') string FROM v1)
,V3 AS (SELECT V_view_name, master.dbo.replace3(string,' | | | | | ',' | | | | | ') string FROM v2)
,V4 AS (SELECT V_view_name, itemNumber,item FROM v3 CROSS APPLY master.dbo.DelimitedSplit_long(String,'@'))
,A as (select v_view_name,S.*,itemnumber, ITEM from S full outer JOIN v4 ON (patindex('%'+referenced_minor_name+'%', ITEM) >1
or (patindex('%'+referenced_entity_name+'%', ITEM) >1 and referenced_minor_name is null)) and S_view_name = v_view_name)
select * from a where v_view_name = '<Name_of_A_View>' order by v_view_name,itemnumber
-- S Views from sys.views.
-- V1/V2/V3 The view definitions.
-- Reformatted, all white space is changed into a single space.
-- Comment is treated specially.
-- A number of symbols generate a split character (comma,FROM,JOIN,ON,SELECT,WHEN,AND) -- Extra symbols can be added if needed.
-- V4 The View is split in Items.
-- A The split views gets combined with the system table.
--
-- Functions:
-- DelimitedSplit_long is a longer variant of DelimitedSplit8k (From the http://www.sqlservercentral.com/ site)
-- replace3 does multiple replaces in one go. (vertical bar is the separator for the distinct replace strings.)
-- sarb net fets / ben brugman.
--
Within the code I use two functions, which are nog generally available.
So the code below, performs the same functions without these two functuin.s
Replace3 has been replaced by multiple replace functions.
master.dbo.DelimitedSplit_long had been replaced with DelimitedSplit8K
Remark the DelimitedSplit8K can only handle 8000 characters, so the views should be limited to approximately 8000 characters, the Long version can handle longer strings.
--
-- Ben Brugman
-- 20180126
--
-- This function will give some insight of usage of fields within a View.
-- This gives help with referenced_minor_name fields and 'mayor_field_names'
--
-- Get all the views from the objects table into ##V
--
select name AS V_view_name, OBJECT_DEFINITION(o.object_id) as string into ##V FROM sys.objects o
WHERE o.type IN ('V') -- and name like '<Name_of_A_View>')
--
-- Get the information about dependencies from the sys.views table into ##S
-- REMARK, Error messages might apair
--
SELECT v.name AS S_view_name, referenced_database_name, referenced_schema_name, referenced_entity_name, referenced_minor_name into ##S
FROM sys.views v
CROSS APPLY sys.dm_sql_referenced_entities (SCHEMA_NAME(v.schema_id) + N'.' + OBJECT_NAME(v.object_id), 'OBJECT')
-- REMARK, Error messages might apair
--
-- Reformat the view
-- 2 2 3 4 5 6 7 8 9 0 1 2 3 4
-- char(13)+char(10)+'|'+char(9)+'|, --|,--|--,|-- ,|,|<komma>| FROM | JOIN | on | SELECT | WHEN | AND '
-- ' | '+'|@<komma> --|@<komma> --|@-- <komma>|@-- <komma>|<komma>@|<komma>|@<FROM> |@<JOIN> |@<ON> |@<SELECT> |@<WHEN> |@<AND> '
-- 1 2 3 4 5 6 7 8 9 0 1 2 3 4
Update ##V Set string = replace(string,char(13)+char(10),' ') -- 1 Make EOL into WhiteSpace
Update ##V Set string = replace(string,char(9) ,' ') -- 2 Make Tab into WhiteSpace
Update ##V Set string = replace(string,', --' ,'@<KOMMA> --') -- 3 Comments directly after a comma, should remain on the same line.
Update ##V Set string = replace(string,',--' ,'@<KOMMA> --') -- 4 Comments directly after a comma, should remain on the same line.
Update ##V Set string = replace(string,'--,' ,'@-- <KOMMA>') -- 5
Update ##V Set string = replace(string,'-- ,' ,'@-- <KOMMA>') -- 6
Update ##V Set string = replace(string,',' ,'<KOMMA>@') -- 7
Update ##V Set string = replace(string,'<KOMMA>' ,',')
Update ##V Set string = replace(string,' FROM ' ,'@FROM ')
Update ##V Set string = replace(string,' JOIN ' ,'@JOIN ')
Update ##V Set string = replace(string,' ON ' ,'@ON ')
Update ##V Set string = replace(string,' SELECT ','@SELECT ')
Update ##V Set string = replace(string,' WHEN ' ,'@WHEN ')
Update ##V Set string = replace(string,' AND ' ,'@AND ')
Update ##V Set string = replace(string,' sarbnetfets ' ,'@NoReplace ')
;With
V2 AS (SELECT V_view_name, itemNumber,item FROM ##v v CROSS APPLY Master.dbo.DelimitedSplit8K(String,'@'))
,A as (select v_view_name,S.*,itemnumber, ITEM FROM ##S S full outer JOIN V2 ON (patindex('%'+referenced_minor_name+'%', ITEM) >1
or (patindex('%'+referenced_entity_name+'%', ITEM) >1 and referenced_minor_name is null)) and S_view_name = v_view_name)
select * into ##Analys_Views from a
-- where v_view_name = '<Name_of_A_View>' -- Optional to limit the amout of data.
order by v_view_name,itemnumber
--
--See the complete view in the 'natural order', remark some items can have more than one referenced_minor_name's.
--
select * from ##analys_views where v_view_name = '<Name_of_A_View>' order by itemnumber, referenced_minor_name
--
-- Search for a specific name in a specific view.
--
declare @search varchar(300) = '%<Name_of_a_field_or_part_of_field>%' -- Wildcards use advised.
select * from ##analys_views where v_view_name = '<Name_of_A_View>' and (referenced_minor_name like @search or item like @search)
--
-- Search for a specific name in all views.
--
declare @search2 varchar(300) = '%<Name_of_a_field_or_part_of_field>%' -- Wildcards use advised.
select * from ##analys_views where (@search2 like referenced_minor_name or item like @search)
--
-- Optional, remove the temporary tables.
--
drop table ##V
drop table ##S
drop table ##analys_views
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply