h1

SQL SERVER: SQL Query To Find Table Dependencies

04/08/2009
We have very large database and today we want to search the tables which are used mostly.

Means tables which are used in Procedures, Constraints, Views, Triggers etc.

I know this is very strange requirement, but we need to do this.

So, I tried to make an query which will help me to find out the top most tables used in other objects as I mentioned

Let me share that sp with all of you:

SELECT TableName, COUNT(*)
FROM (
Select Distinct
o.Name ‘TableName’,
op.Name ‘DependentObject’
From SysObjects o
INNER Join SysDepends d ON d.DepId = o.Id
INNER Join SysObjects op on op.Id = d.Id
Where o.XType = ‘U’
Group by o.Name, o.Id, op.Name
) x
GROUP BY TableName
ORDER BY 2 desc

So, I made my life easy, by using this. I can get the list if Tables which are used most.

Let me know if it helps you in any way.

About these ads

4 comments

  1. Hi Tejas,

    I am a regular reader of your articles, and really impressed by your way of giving simplest solution…

    I got stuck at a point, and hope you could help me out in that….

    We are developing a multilanguage website.

    I have a table Product with following columns
    productid (bigint)
    code nvarchar(20)
    name nvarchar(250)
    sname nvarchar(250)

    Here full text index is applied on code, name and sname column. Name and Sname column can contain data in different languages. Now on my websit we have a search box, in which user can enter search keywords in any language. and I a have a function like follow, which is not returing me proper result
    —————————————-
    Note : @searchstr = ‘ようこそゴールデ’

    CREATE FUNCTION [dbo].[FN_SearchProduct] (@searchstr nvarchar(50))
    RETURNS TABLE
    AS
    RETURN (
    SELECT productid
    FROM product p
    WHERE freetext(*, @searchstr)
    )
    —————————————-

    I can not use N infront of @searchstr, it gives error. Please let me know if there is any way to get proper result.

    Thanx..


  2. it was helpful for me


  3. Hi,
    Again Affan here..need another help… I am having the result as

    a1 – 2 Nos, a2 – 530 Nos, a3 – 2 Boxes and i need the output as

    a1 – 2 Nos
    a2 – 530 Nos
    a3 – 2 Boxes

    Thanks in advance


  4. Hi Arfan,

    Please read my article: http://sqlyoga.com/2009/05/sql-server-get-comma-separated-values.html. Let me know if you face any issue.

    Thanks,
    Tejas
    SQLYoga



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: