Views and Dependencies

  • 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)

  • 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.

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

  • 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

  • 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/

  • Peter H, thanks for the SQL.

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

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

  • 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.

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

  • 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

  • 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.

  • 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)

  • 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]

  • 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
  • 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