July 9, 2012 at 10:02 pm
Comments posted to this topic are about the item Views and Dependencies
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
July 10, 2012 at 5:04 am
It occurs to me that our organization and developers need a better method to monitor modifications on specific objects, either DDL or DML, when then might affect "custom" or "one off" objects.
In the (unlikely) event that you're interested only in the downstream impact of changes which do not transcend your SQL database, the following may be of some use to you:
SELECT
referencing_schema_name,
referencing_entity_name,
referencing_id,
referencing_class_desc,
is_caller_dependent
FROM sys.dm_sql_referencing_entities ('<object_name>', 'OBJECT');
GO
Bear in mind that impact analysis is a fundamental part of maintenance and Change Control and needs buy in from everyone to be effective. The work and effort involved in achieving a good mechanism is typically substantial (and costly) so needs to be balanced against the benefits of implementing it. Obviously the above snippet will not highlight the impact to items outside your database(s) so, if that constitutes a significant portion of your solution, you should probably start thinking about a lineage-impact model.
July 10, 2012 at 8:01 am
I guess this is another example of why we have recommended for years not to use the syntax SELECT *
July 10, 2012 at 11:23 am
For those who are looking for an easier way out, I put together this sql that (hopefully) will identify the views that are older than the last table modify date, and call for only them to be refreshed. I also added the schema name, because not everything is always "DBO". Someone could wrap this in a cursor and execute directly, say every night, or after each prod release, to ensure that no views were missed:
SELECT DISTINCT 'EXEC sp_refreshview ''' + OBJECT_SCHEMA_NAME(v.object_id) + '.' + v.name + ''''
--, reference=OBJECT_SCHEMA_NAME(sed.referenced_major_id) + '.' + OBJECT_NAME(sed.referenced_major_id)
--, t.modify_date, v.modify_date,*
FROM sys.objects v
INNER JOIN sys.sql_dependencies sed ON sed.object_id = v.object_id
INNER JOIN sys.objects t ON t.object_id = sed.referenced_major_id
WHERE v.type = 'V' --AND OBJECT_NAME(sed.referenced_major_id) LIKE 'MyTable%'
and t.modify_date >= v.modify_date
order by 1
July 10, 2012 at 12:48 pm
Thank you for posting the article.
I would point out that it is generally advisable to avoid relying on the order of the columns in the base table. When the order matters, specify the order you want the columns to appear in. When the order will matter frequently (and in a consistent way), hide it behind a view that specifies the columns and their order.
Also, I would generally advise against using select * for anything other than probing an unfamiliar table. If you want to avoid the typing, there are several refactoring tools that integrate into SSMS that will expand a * for you.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
July 10, 2012 at 2:31 pm
Peter H, thanks for the SQL.
One thing I was wondering about is why you have the "order by 1" phrase.
July 10, 2012 at 2:53 pm
Review by object name is easier, so order by "object name". (Order by 1 means order by the first column returned).
July 10, 2012 at 3:16 pm
Peter H (7/10/2012)
Review by object name is easier, so order by "object name". (Order by 1 means order by the first column returned).
Duh, I knew that. Guess my brain went to sleep. 🙂
Thanks for your patience.
July 10, 2012 at 7:15 pm
Thank you for posting the article.I have encountered a problem before. Changed the base table column data type, but the view didn't changed accordingly. So need to manually refresh the view. This is inconvenient, i was wondering why SQL Server didn't automatically update the metadata?
July 11, 2012 at 12:05 am
By the way, please don't use date literals like this:
'MM/DD/YY'
This might will not work on system which use other language settings (or datetime settings) than english.
Please always use the ISO literals like this instead:
'YYYYMMDD'
They do work. Always: http://www.karaszi.com/SQLServer/info_datetime.asp
July 11, 2012 at 2:29 am
Peter H (7/10/2012)
For those who are looking for an easier way out, I put together this sql that (hopefully) will identify the views that are older than the last table modify date, and call for only them to be refreshed. I also added the schema name, because not everything is always "DBO". Someone could wrap this in a cursor and execute directly, say every night, or after each prod release, to ensure that no views were missed
Even if you have a complete picture of the downstream impact to changing the structure of the base-table I'd advise against automating the refresh.
First of all, sys.sql_dependencies is only good to one level deep. What happens when the view needing to be refreshed is referenced by another object in your data layer such as another view or stored procedure? Obviously you could redesign the script to recurse through every dependency level but you have to consider if that does you any favours.
If there are dependencies on the refreshed objects outside the data layer these will most like require editing too (a SSIS package, for example, would require its source meta data updated at the very least).
If these external dependencies haven't been accommodated when the change to the base-table is deployed, an automated refresh is simply cascading the inconsistency downstream. If the inconsistency is a show-stopper, you'll be looking at a rollback - which will probably be more bother than refreshing the dependencies manually!
That said, if you are fortunate enough to have a complete lineage-impact matrix of your entire solution and can accommodate all downstream dependencies, a suitable script would be a valuable addition to your deployment packages.
I'd still not automate it though.
July 11, 2012 at 5:39 am
That said, if you are fortunate enough to have a complete lineage-impact matrix of your entire solution and can accommodate all downstream dependencies, a suitable script would be a valuable addition to your deployment packages.
Funny, that is what I was also thinking about before work this morning!
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
July 11, 2012 at 6:30 am
To gain another perspective, and method to "refresh/update" the views meta data may I suggest looking at an older QOD at:
http://www.sqlservercentral.com/questions/Views/88916/
What happens behind the scene, so to speak, is shown in the Execution plans (posted as support for the correct answer(s) to the QOD.
July 11, 2012 at 8:05 am
e-ghetto (7/11/2012)
By the way, please don't use date literals like this:
'MM/DD/YY'This might will not work on system which use other language settings (or datetime settings) than english.
Please always use the ISO literals like this instead:
'YYYYMMDD'They do work. Always: http://www.karaszi.com/SQLServer/info_datetime.asp
What is I use 'YYYY-MM-DD'?
July 11, 2012 at 11:25 pm
What is I use 'YYYY-MM-DD'?
I can't promise 'YYYY-MM-DD' will work regardless of your current settings.
'YYYYMMDD' does always work. Just stick to this little rule, it doesn't hurt and you won't be disappointed 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply