Это видео недоступно.
Сожалеем об этом.
SQL Tutorial - How to search text in a Stored Procedure
HTML-код
- Опубликовано: 6 сен 2024
- Learn how to search text in a stored procedure, if you want to know what stored procedures are inserting, updating or deleting data in a table this tutorial will show you how. Using a combination of system catalog views, sys.objects and sys.sql_modules you will not only be able to find all stored procedures that reference a table but other objects too such as views, triggers and functions.
Further reading can be found on Microsoft Docs:
sys.objects
docs.microsoft...
sys.sql_modules
docs.microsoft...
Your videos are a godsend! Thank you!
Thanks so much.
Really useful. I have used something similar in another DBMS but didn't know about this in SQL Server
Thanks John 👍
I use this all the time such a great time saving tip
Oh yes, massive time saver.
Wow this is amazing, I can't wait to share this with my team!
Excellent, it always comes in handy 👍
'WANTED Bearded dev' By learning community
I agree SSMS tools can be somewhat unreliable and rarely if ever use them, however have never had any issues with using sys.dm_sql_referencing_entities /sys.dm_sql_referenced_entities. The problem with trying to parse object definition text is you can't reliably be sure you're not finding a comment, alias etc, however joining to these system functions will filter your results to the objects that *do* reference the target table.
They are some very useful DMFs you have pointed out there, I still think there is some use to searching the definition though but you are right and as I tried to point out it's not perfect when searching. For example what if we wanted to find any stored procedures where deletes were performed or any that utilise temporary tables or are missing error handling.
Thank so much ❤️ it really helps me 😇
No problem.
@@BeardedDevData keep it up 👍
hi i really love your videos can you make a video on the new datebucket function in sql server 2022 it looks interesting but i want to see someone teach it
That's a great idea, I'm just taking a break until early next year then I will be back making videos more regularly.
Hey there,
God bless your efforts.
I have only a general enquiry as a new sql learner.
Suppose i created one table on sql management studio for data entry purpose...
How can i compile this sql table into a windows application? In what compiler should i placed it?
The reason i am asking that i would like to discover how an sql application is finally presented as an application...
THanks for taking care of this.
Hi Hasan, in all honesty I don't think I'm the best person to answer that question, it's been a long time since I did anything like that and I've only ever dabbled in it.
Hi
Thank you so much for such a wonderful informative video.
I have a doubt, in my db i have a table with 9+ million records. All the data types are predefined as nvarchar of max.
Is there any way to summarise the data, data profiling, and get the right data type so that we can correct this mesh as this impacts really adversely on performance
Hi Suraj, that's a real pain, I would view some of the data initially see if there is anything obvious you can see, some columns might be dates, some might be integers etc. You can then use functions like CAST or CONVERT for the obvious ones, this will alert to any failures, for strings you can use functions such as LEN to test how long they are. I'm going to put this on my list of videos cause I here about this a lot.
@@BeardedDevData i think this may work on small set of data or table, table having 9m of records would be realy a painful process.
Thankyou for your suggestion
It would take some time but the alternative is significantly more painful, if people have access to the data and are querying it, it is likely they will do these type of conversions within those queries, that will be a lot more costly. The data will need to be converted to be useable so its better to do that as early as possible and prevent happening again. 9m rows isn't really a large amount of data, will only take a few minutes to do depending on the amount of columns, data types, of course that depends on what your database is running on to.
Thankyou 😊, it makes sense
Hey! When are you going to post some new content?
Hi Caribou, it's a good question, I'm struggling for time with other commitments at the moment but I definitely will return, just want to get to a position where I can consistently upload content.
How to find column name in proc
Example goodsvalue+taxamount billamount
i find the billamount in proc give the output text=goodsvalue+taxamount
Any possible?
You can search anything as long as its in the definition of the stored procedure but remember you are just searching text. Can just pass in the column name, can even search expressions such as goodsvalue+taxamount but this won't return any stored procedures where the expression is written as goodsvalue + taxamount. Hopefully that provides some clarity.
Thank you for your reply
Exec sp_helptext @object='procedure name',@columnname=billamount
How use procedure?
Hi, my sincere request is to please walk us through on using 'if-else' in sql
Sure, I'm just having a break to spend time with the family then I will be back regularly uploading early next year, I will put this on my list to cover.
What is the difference between ; and GO ? Its the same, is it not?
No, they are not the same, ; is used to separate statements and isn't typically necessary, GO is not actually SQL, I don't believe but it is recognised by clients to send the statements to the server.
@@BeardedDevData I never used ; before and had never problems with not using it.
Like I say, it isn't typically necessary, the only time that I'm aware of that you have to use it is if you are declaring a CTE after some other code, if you don't use it you will get an error.
Would you be willing to answer a few questions I have about a prject I am working in with SSIS?
Sure, just drop me a message to askbeardeddev@gmail.com and I will come back to you as soon as I can.
You are being too quiet?
Hi Caribou, I'm struggling for time at the moment due to family and work commitments, I am quietly working away on things in the background and will be back in January consistently uploading content.