April 9, 2015 at 11:53 am
hello,
I am looking for a script, that returns all SPs, which related to a given table, incl. the access type.
For example: the SPs SP_test1, SP_test2, SP_test3 are dependencies of MyTable
This should be the result:
MyTable
--> SP_test1: select
--> SP test1: update
--> SP_test1: drop
--> SP test2: delete
--> SP_test3: insert
There are a lot of scripts, who returns the related SPs. But how can I get the access type of this?
Can someone helping me?
Martina
April 9, 2015 at 12:15 pm
maurer.martina (4/9/2015)
hello,I am looking for a script, that returns all SPs, which related to a given table, incl. the access type.
For example: the SPs SP_test1, SP_test2, SP_test3 are dependencies of MyTable
This should be the result:
MyTable
--> SP_test1: select
--> SP test1: update
--> SP_test1: drop
--> SP test2: delete
--> SP_test3: insert
There are a lot of scripts, who returns the related SPs. But how can I get the access type of this?
Can someone helping me?
Martina
Quck suggestion
😎
exec sp_depends N'[schema].
';
April 9, 2015 at 12:42 pm
One caveat of Eirikur's excellent suggestion is that it will NOT find any references if your procedures contain dynamic sql.
_______________________________________________________________
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/
April 9, 2015 at 1:11 pm
Sean Lange (4/9/2015)
One caveat of Eirikur's excellent suggestion is that it will NOT find any references if your procedures contain dynamic sql.
Good point, then we use the sys.all_sql_modules
😎
SELECT
OBJECT_NAME(ASM.object_id) AS OBJ_NAME
,OBJECT_SCHEMA_NAME(ASM.object_id) AS OBJ_SCHEMA_NAME
,ASM.definition
FROM sys.all_sql_modules ASM
WHERE ASM.definition LIKE N'%tablename%'
AND ASM.object_id > 100;
April 9, 2015 at 1:18 pm
Eirikur Eiriksson (4/9/2015)
Sean Lange (4/9/2015)
One caveat of Eirikur's excellent suggestion is that it will NOT find any references if your procedures contain dynamic sql.Good point, then we use the sys.all_sql_modules
😎
SELECTOBJECT_NAME(ASM.object_id) AS OBJ_NAME
,OBJECT_SCHEMA_NAME(ASM.object_id) AS OBJ_SCHEMA_NAME
,ASM.definition
FROM sys.all_sql_modules ASM
WHERE ASM.definition LIKE N'%tablename%'
AND ASM.object_id > 100;
Thanks!!! Not sure why I pointed out a possible concern and stopped short of providing an alternative. Been a heck of a week around the office...thankfully the weekend is another day or so away. 😉
_______________________________________________________________
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/
April 9, 2015 at 1:28 pm
Sean Lange (4/9/2015)
Thanks!!! Not sure why I pointed out a possible concern and stopped short of providing an alternative. Been a heck of a week around the office...thankfully the weekend is another day or so away. 😉
No worries, I really appreciate the input!:-)
😎
April 10, 2015 at 3:03 am
sorry, but I am looking for something different...
Your scripts only returns the depend SPs, but NOT the access-type.
I need both.
with "access-type" I mean:
does the SP contains a INSERT-statement and/or
a UPDATE-statement and/or
a DELETE-statement and/or
a DROP-statement and/or
a TRUNCATE-statement and/or
a INSERT-statement
...and so on
For Example: the SP sp_test1 contains
a create-statement for my table
a select to table xxx
a insert for my table
a truncate for table xxx
The wanted script returns the info "create" and "insert"
"select" and "truncate" were ignored because of the different table.
Maybe the field "definition" of the script of Eirikur could be parsed, but how?
any ideas?
April 10, 2015 at 3:53 am
Try this
😎
SELECT
OBJECT_NAME(ASM.object_id) AS OBJ_NAME
,OBJECT_SCHEMA_NAME(ASM.object_id) AS OBJ_SCHEMA_NAME
,CASE WHEN ASM.definition LIKE N'%UPDATE%' THEN 1 ELSE 0 END AS IS_UPDATE
,CASE WHEN ASM.definition LIKE N'%SELECT%' THEN 1 ELSE 0 END AS IS_SELECT
,CASE WHEN ASM.definition LIKE N'%DELETE%' THEN 1 ELSE 0 END AS IS_DELETE
,CASE WHEN ASM.definition LIKE N'%DROP%' THEN 1 ELSE 0 END AS IS_DROP
,CASE WHEN ASM.definition LIKE N'%TRUNCATE%' THEN 1 ELSE 0 END AS IS_TRUNCATE
,CASE WHEN ASM.definition LIKE N'%INSERT%' THEN 1 ELSE 0 END AS IS_INSERT
,ASM.definition
FROM sys.all_sql_modules ASM
WHERE ASM.definition LIKE N'%table_name%'
AND ASM.object_id > 100;
April 10, 2015 at 4:10 am
I've been through an exercise like this across multiple servers before, although I was lucky enough that the type of operation wasn't a requirement as it is here. I used sys.sql_dependencies, but that's not available in SQL Server 2005, so you have to search in the procedure definition, but then you have comments and dynamic SQL and such like, which you can use regular expressions for, but that doesn't catch everything. And what happens if you have a table called Table and one called MyTable, or a table that happens to have the same name as a schema, or an index hint that happens to contain the name of the table? It's a huge minefield. I ended up just accepting that it was as accurate as I could make it, but inevitable not 100% accurate.
John
April 10, 2015 at 4:28 am
...minefield is the right word:-)
@Eirikur: it doesn't work, because it returns also the "is truncate" when the Sp contained a truncate of a table, i'm not interessting in.
See my example...Your script would returns all 4 access-types; but i am only interessted in the two who access myTable.
...
CASE WHEN ASM.definition LIKE N'%TRUNCATE%MyTable%' THEN 1 ELSE 0 END AS IS_TRUNCATE
...
won't work also, if the SP contains
truncate table XXXX;
select * from MyTable;
It isn't easy...
April 10, 2015 at 4:36 am
If you're determined do this, you'll need to write yourself a parser. As far as I know, Microsoft haven't published the code for their SQL parser, so you'd be on your own. Another alternative is to go through a one-off exercise of documenting what each stored procedure does, and introduce strict source control so that all changes are captured in the documentation. You might want to consider using extended properties for that.
John
April 10, 2015 at 5:04 am
new thought: a kind of syntax-check
Example-Statement: delete from mytable
allowed characters between "delete" and "from" and "myTable" are only blanks, right?
If I erase the blanks in the definition, there must be the string "deletefrommytable"..
I tried this, but it has errors (sorry i am a newbe):
use msdb
SELECT
OBJECT_NAME(ASM.object_id) AS OBJ_NAME
,OBJECT_SCHEMA_NAME(ASM.object_id) AS OBJ_SCHEMA_NAME
,CASE WHEN (Replace(ASM.definition;" ";"")) LIKE N'%UPDATEbackupset%' THEN 1 ELSE 0 END AS IS_UPDATE
,CASE WHEN (Replace(ASM.definition;" ";"")) LIKE N'%SELECT%' THEN 1 ELSE 0 END AS IS_SELECT
,CASE WHEN (Replace(ASM.definition;" ";"")) LIKE N'%DELETEFROMbackupset%' THEN 1 ELSE 0 END AS IS_DELETE
,CASE WHEN (Replace(ASM.definition;" ";"")) LIKE N'%DROPTABLEbackupset%' THEN 1 ELSE 0 END AS IS_DROP
,CASE WHEN (Replace(ASM.definition;" ";"")) LIKE N'%TRUNCATETABLEbackupset%' THEN 1 ELSE 0 END AS IS_TRUNCATE
,CASE WHEN (Replace(ASM.definition;" ";"")) LIKE N'%INSERTINTObackupset%' THEN 1 ELSE 0 END AS IS_INSERT
,ASM.definition
FROM sys.all_sql_modules ASM
WHERE ASM.definition LIKE N'%backupset%'
AND ASM.object_id > 100;
And there are 3 unsolved problems:
a)how handle selects?
b)the schema-theme (dbo.backupset,...)
c)what, if there is a table called "backupset2"?
is there a posibility to replace a few blanks to only one blank? That would solve problem c)
April 10, 2015 at 5:17 am
No, it's not as simple as that, and the more you look at it, the more complications you'll see. What about tabs, carriage returns, schema names, comments and other stuff? What happens where DELETE FROM is used in some places and just DELETE in others, and similarly for INSERT and INSERT INTO? Regular expressions (have you looked at those?) can help up to a point, but you'll spend a lot of time working out how they work, only to come to the conclusion that you need a proper parser (at least that was my experience).
As for boiling down multiple spaces to single spaces, I seem to remember an article on this site by Jeff Moden on that very topic a couple of years ago.
John
April 10, 2015 at 6:45 am
John Mitchell-245523 (4/10/2015)
No, it's not as simple as that, and the more you look at it, the more complications you'll see. What about tabs, carriage returns, schema names, comments and other stuff? What happens where DELETE FROM is used in some places and just DELETE in others, and similarly for INSERT and INSERT INTO? Regular expressions (have you looked at those?) can help up to a point, but you'll spend a lot of time working out how they work, only to come to the conclusion that you need a proper parser (at least that was my experience).As for boiling down multiple spaces to single spaces, I seem to remember an article on this site by Jeff Moden on that very topic a couple of years ago.
John
This is one of those things that sound good at first, but usually end up having to be done by hand. If you need a high level of reliability on the access type, start with the dependency list and work your way through classifying each item you find.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 10, 2015 at 7:00 am
maurer.martina (4/10/2015)
new thought: a kind of syntax-checkExample-Statement: delete from mytable
allowed characters between "delete" and "from" and "myTable" are only blanks, right?
If I erase the blanks in the definition, there must be the string "deletefrommytable"..
I tried this, but it has errors (sorry i am a newbe):
use msdb
SELECT
OBJECT_NAME(ASM.object_id) AS OBJ_NAME
,OBJECT_SCHEMA_NAME(ASM.object_id) AS OBJ_SCHEMA_NAME
,CASE WHEN (Replace(ASM.definition;" ";"")) LIKE N'%UPDATEbackupset%' THEN 1 ELSE 0 END AS IS_UPDATE
,CASE WHEN (Replace(ASM.definition;" ";"")) LIKE N'%SELECT%' THEN 1 ELSE 0 END AS IS_SELECT
,CASE WHEN (Replace(ASM.definition;" ";"")) LIKE N'%DELETEFROMbackupset%' THEN 1 ELSE 0 END AS IS_DELETE
,CASE WHEN (Replace(ASM.definition;" ";"")) LIKE N'%DROPTABLEbackupset%' THEN 1 ELSE 0 END AS IS_DROP
,CASE WHEN (Replace(ASM.definition;" ";"")) LIKE N'%TRUNCATETABLEbackupset%' THEN 1 ELSE 0 END AS IS_TRUNCATE
,CASE WHEN (Replace(ASM.definition;" ";"")) LIKE N'%INSERTINTObackupset%' THEN 1 ELSE 0 END AS IS_INSERT
,ASM.definition
FROM sys.all_sql_modules ASM
WHERE ASM.definition LIKE N'%backupset%'
AND ASM.object_id > 100;
And there are 3 unsolved problems:
a)how handle selects?
b)the schema-theme (dbo.backupset,...)
c)what, if there is a table called "backupset2"?
is there a posibility to replace a few blanks to only one blank? That would solve problem c)
Quick point, I run quite few queries like the one I posted earlier which give me flags on keywords or keyword combinations, as a part of a code quality metrics collection. The output is a spreadsheet with few tens of graphs and filtered tables, each of which focuses on a specific problem area/domain. That's where I normally draw the line of automation, from there I visually inspect the code, as far as I'm aware of there are no tools available which give accurate enough analysis of the code.
😎
Just to underline the complexity of the task think of what is happening at the "compilation" level:
lexical analysis, parsing, syntax-directed translation, abstract syntax trees, types and type checking, dataflow analysis, optimization etc.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply