Views and Dependencies

Question

Views and Dependencies

DB_Newbie2007

SSCarpal Tunnel

Points: 4454
More actions
July 9, 2012 at 10:02 pm

#392701

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)

Andrew Diniz SSCommitted Points: 1852 More actions · Answer 1

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.

btaylor 78431 SSC Journeyman Points: 93 More actions · Answer 2

I guess this is another example of why we have recommended for years not to use the syntax SELECT *

Peter H SSC Enthusiast Points: 127 More actions · Answer 3

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

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 4

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/

Marcia J SSCertifiable Points: 5649 More actions · Answer 5

Peter H, thanks for the SQL.

One thing I was wondering about is why you have the "order by 1" phrase.

Peter H SSC Enthusiast Points: 127 More actions · Answer 6

Review by object name is easier, so order by "object name". (Order by 1 means order by the first column returned).

Marcia J SSCertifiable Points: 5649 More actions · Answer 7

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.

DBA328 Hall of Fame Points: 3952 More actions · Answer 8

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?

e-ghetto SSChasing Mays Points: 616 More actions · Answer 9

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

Andrew Diniz SSCommitted Points: 1852 More actions · Answer 10

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.

DB_Newbie2007 SSCarpal Tunnel Points: 4454 More actions · Answer 11

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)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 12

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.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

BudaCli Hall of Fame Points: 3404 More actions · Answer 13

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'?

What you don't know won't hurt you but what you know will make you plan to know better

e-ghetto SSChasing Mays Points: 616 More actions · Answer 14

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

Views and Dependencies

Cookies on SQLServerCentral