Column, Table and Database and how do we find which one is where ?

Hello Again!!!

Last evening I went to a SQL user group meeting and heard an interesting conversation between two geek. The back ground is that this person was looking for SQL Server Developer person for his company (A JOB man!!!!).

So naturally I was interested to eve-drop into their conversation (not a good thing to anyway) and I heard this bigger geek asking lesser geek that how do can you give me a list of all the tables where a particular column is used.

So me trying to be smart (.. wait a minute!!!! I am not smart but I am trying to be geek...), thought hmmm that must be stored somewhere in system table or view some where. May be sys.objects? that was my first guess, then sys.columns??? bingo.. I got it..

So this morning I can in early for my work and thought let me give a try and see what all I can come up with. So after few hour of brain-storming on this (Include google search too.. and thanks to all those wonderful people who write blogs about their work), I finally figured out what I need to write.

So here I am sharing with you guys.. (Hope someday it will come handy to someone!!)

Let's work with AdventureWorks database.

Lets look at sys.object table first.

This table list all the objects that are present in your database. If someone ask you a question, give a query which give me all the table in database (not talking about system tables but user tables), this is the place where I can go and get the information from.

Do a basic select * from sys.objects will give you all the object in that particular data base. Now going through this, you will see name, object_id, schema_id, type and type_desc beside other columns.

One thing here is that object_id and name is ideally same thing. You can convert object_id column to name field by using Object_Name function like this.

Select OBJECT_NAME(OBJ.Object_ID) As Object_ID_Name, OBJ.Name from sys.objects OBJ

The two column returned is identical.

So coming to original question, "Give me query which will give me all the table in a database?"  we want to know that table he want to know are "USER_TABLE" which will be under type_desc column and parent_object_id will be zero (0) because these are table and are always parents.

So my query will look something like this.



SELECT Object_Name(O.object_id) 'Name'
,O.type_desc
,O.type
,O.schema_id
,O.create_date
FROM sys.objects O
WHERE O.parent_object_id = 0
AND O.type_desc = 'USER_TABLE'
ORDER BY o.Schema_id
,O.NAME;

This will return all the table in my database which has been created by users

:)

Next question he asked was give me a list of all the table where a particular column is used? for example show me all the table where column "AddressLine1" is being referred?


To do this, we have to look at another system table called sys.columns. This table list all the columns defined in our database. Also we want to what is datatype of "AddressLine1" is it varchar, char, nvarchar or some smart person defined as int.


To do so


SELECT C.NAME 'Col Name'
,Object_Name(C.object_id) 'Table Name'
,T.NAME 'Data Type'
FROM sys.columns C
INNER JOIN sys.types t ON C.system_type_id = t.system_type_id
WHERE C.NAME = 'AddressLine1';



 More to come here... If you have questions, feel free to ask...