It's been sometime now I have been working on SQL Server and found somethings that are odd and some rely cool things about it. Though there is still an infinite left to learn I'm sure, I have some tips and tricks which were a hellava use to me. So thought of jotting them down.
Lets start with some thing very small and usefull. Many of you may have observed that the sql server 2000 is buggy in showing the dependency of the objects.
It is very irksome in SQL2000 Query Analyzer to find the dependency of the tables on different objects. Reason being most of the time the result is incomplete.
It’s just a matter of few moments now and after that you will be able to find dependencies of any kind of object be it a stored procedure, table or sql jobs.
NOTE: these queries are only limited to sql server 2000
We will utilize few systables for that purpose syscomments , sysobjects , sysjobs and sysjobsteps. You will need DBA rights to access these tables.
First let me show you how a dependency of a table is shown in the Query Analyzer in SQL 2000 GUI. Here we find the dependency of the table tv_family as shown in Image1.
Here 5 dependencies are shown which is incomplete. Now we will use the systables to find the actual dependencies of the table.
The Image2 mentions the query and the correct dependency.Here we see there are 161 objects of different types dependent on the table where as the GUI showed only 5.
Now let me give you a small brief as to how this simple query does this magic.
The systable sysobjects contains all the kind of objects created in the database concerned. They have a unique id now syscomments consist of text field which contains the whole script in case of a Stored procedure, Function, etc.. Now as the query suggests we essentially hunt for the text ‘tv_family’ in the join and extract the names of all the sysobjects and then order by as per our needs.
The above shown is a very generic query to find any object using a like query so you may get additional objects names than the name you are searching for.
Note: to be continued...