January 23, 2017 at 2:41 pm
Sean Lange - Monday, January 23, 2017 2:24 PMWelsh Corgi - Monday, January 23, 2017 9:16 AMI hate to ask but how do I modify the following code to include the Table Name.I would greatly appreciate any help.
Thank you.
SELECT nameFROM sys.procedures
WHERE Object_definition(object_id) LIKE '%LastIDNumber%'
ORDER BY Name
Simply put, you can't do this with a query. You would use that query to identify all procedures that reference your lookup table. From there it is going to require a human to look at that code and decide what table(s) are affected. You could probably write code to do this but it would take days if not weeks to accommodate all the possible permutations. It would be faster to simply open each procedure and document what you find. It is really hard to offer much concrete advice because we don't really know what you are trying to do here. Maybe it is to identify all the tables that get their Primary Key value from that lookup table or maybe it is to identify how much work would be involved to use an identity instead. With some context we can probably help but with just vague explanations it is really hard to offer much more than a vague explanation of what we might try to do.
I believe that this is possible with the query store in SQL 2016, but I haven't had a chance to play around with SQL 2016, yet.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 23, 2017 at 2:48 pm
drew.allen - Monday, January 23, 2017 2:41 PMSean Lange - Monday, January 23, 2017 2:24 PMWelsh Corgi - Monday, January 23, 2017 9:16 AMI hate to ask but how do I modify the following code to include the Table Name.I would greatly appreciate any help.
Thank you.
SELECT nameFROM sys.procedures
WHERE Object_definition(object_id) LIKE '%LastIDNumber%'
ORDER BY Name
Simply put, you can't do this with a query. You would use that query to identify all procedures that reference your lookup table. From there it is going to require a human to look at that code and decide what table(s) are affected. You could probably write code to do this but it would take days if not weeks to accommodate all the possible permutations. It would be faster to simply open each procedure and document what you find. It is really hard to offer much concrete advice because we don't really know what you are trying to do here. Maybe it is to identify all the tables that get their Primary Key value from that lookup table or maybe it is to identify how much work would be involved to use an identity instead. With some context we can probably help but with just vague explanations it is really hard to offer much more than a vague explanation of what we might try to do.
I believe that this is possible with the query store in SQL 2016, but I haven't had a chance to play around with SQL 2016, yet.
Drew
Neither have I, Drew. Haven't made it a priority to install 2016 at home after my first failed attempt. Our production systems that I help support are running SQL Server 2008 R2 SE, but I do have SQL Server 2014 DE installed on my work laptop.
Guess I should make it a priority and get 2016 installed.
January 23, 2017 at 2:56 pm
Lynn Pettis - Monday, January 23, 2017 2:48 PMdrew.allen - Monday, January 23, 2017 2:41 PMSean Lange - Monday, January 23, 2017 2:24 PMWelsh Corgi - Monday, January 23, 2017 9:16 AMI hate to ask but how do I modify the following code to include the Table Name.I would greatly appreciate any help.
Thank you.
SELECT nameFROM sys.procedures
WHERE Object_definition(object_id) LIKE '%LastIDNumber%'
ORDER BY Name
Simply put, you can't do this with a query. You would use that query to identify all procedures that reference your lookup table. From there it is going to require a human to look at that code and decide what table(s) are affected. You could probably write code to do this but it would take days if not weeks to accommodate all the possible permutations. It would be faster to simply open each procedure and document what you find. It is really hard to offer much concrete advice because we don't really know what you are trying to do here. Maybe it is to identify all the tables that get their Primary Key value from that lookup table or maybe it is to identify how much work would be involved to use an identity instead. With some context we can probably help but with just vague explanations it is really hard to offer much more than a vague explanation of what we might try to do.
I believe that this is possible with the query store in SQL 2016, but I haven't had a chance to play around with SQL 2016, yet.
Drew
Neither have I, Drew. Haven't made it a priority to install 2016 at home after my first failed attempt. Our production systems that I help support are running SQL Server 2008 R2 SE, but I do have SQL Server 2014 DE installed on my work laptop.
Guess I should make it a priority and get 2016 installed.
Same here. That would be pretty amazing if it can do stuff like that. Query store is something I have heard about but not really looked into very deeply yet. Sounds fascinating.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 23, 2017 at 3:01 pm
Sean Lange - Monday, January 23, 2017 2:56 PMLynn Pettis - Monday, January 23, 2017 2:48 PMdrew.allen - Monday, January 23, 2017 2:41 PMSean Lange - Monday, January 23, 2017 2:24 PMWelsh Corgi - Monday, January 23, 2017 9:16 AMI hate to ask but how do I modify the following code to include the Table Name.I would greatly appreciate any help.
Thank you.
SELECT nameFROM sys.procedures
WHERE Object_definition(object_id) LIKE '%LastIDNumber%'
ORDER BY Name
Simply put, you can't do this with a query. You would use that query to identify all procedures that reference your lookup table. From there it is going to require a human to look at that code and decide what table(s) are affected. You could probably write code to do this but it would take days if not weeks to accommodate all the possible permutations. It would be faster to simply open each procedure and document what you find. It is really hard to offer much concrete advice because we don't really know what you are trying to do here. Maybe it is to identify all the tables that get their Primary Key value from that lookup table or maybe it is to identify how much work would be involved to use an identity instead. With some context we can probably help but with just vague explanations it is really hard to offer much more than a vague explanation of what we might try to do.
I believe that this is possible with the query store in SQL 2016, but I haven't had a chance to play around with SQL 2016, yet.
Drew
Neither have I, Drew. Haven't made it a priority to install 2016 at home after my first failed attempt. Our production systems that I help support are running SQL Server 2008 R2 SE, but I do have SQL Server 2014 DE installed on my work laptop.
Guess I should make it a priority and get 2016 installed.Same here. That would be pretty amazing if it can do stuff like that. Query store is something I have heard about but not really looked into very deeply yet. Sounds fascinating.
I was doing something similar with the cached plans, but it was frustrating, because the caches on the dev, int, and stage servers were not very complete and I didn't want to run my queries on the prod server.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply