Home > Query, SQL > BISQL # 47 : How to find Details of Any Column in Entire Database in One Query

BISQL # 47 : How to find Details of Any Column in Entire Database in One Query

 

Hi Friends,

Heading of this article must have been attracted you,But its really true you can achieve it only by having very simple queries

When we have 100 table in our database its hard to go and find where exactly my column exist and again to find the detail we have still dig into our analysis !! What to do in this type of condition ?

So at most of time we are getting this requirement such as in which table I will get this particular column don’t even wait or think for any movement just directly use the following query

Based on requirement also we can further divided in to two queries

1.Search Column name for Identity and for check null

2. Search Column name for Data type and length of column

Lets have look one by on with query as well as example with our all time favourite database Adventure Works DW DB

1.Search Column name for Identity and for check null

In this we have to find column name as well as we want to check it for whether it is identity or null

Following is script which I love most

image

I know it you can copy paste it but that was just for where to give input to column name

Lets we need to find all the column name Related to Employee so in this case I will just fire following query which you can copy paste too 🙂

SELECT  t.name AS 'Table Name',
        SCHEMA_NAME(schema_id) AS 'Schema Name',
        c.name AS 'Column Name',
        c.is_identity as 'Is Columen Identity',
        c.is_nullable as 'Is Columen Nullable'
FROM    sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE   c.name LIKE '%Emp%' 
ORDER BY 'Schema Name', 'table name'; 

Which have following output in SSMS plane

image

In above you can see all list of Column name having Emp as keyword in between in column name

2. Search Column name for Data type and length of column

In this we have to find column name as well as we want to check its Data Type also the length of column

You just have write whatever column name you want search in following script

image

Lets find of EMP details as we have found in above query ,We need to find all the column name Related to Employee so in this case I will just fire

Following is required script

SELECT  t.name AS 'Table Name',
        SCHEMA_NAME(schema_id) AS 'Schema Name',
        c.name AS 'Column Name',
        c.is_identity as 'Is Columen Identity',
        c.is_nullable as 'Is Columen Nullable'
FROM    sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE   c.name LIKE '%Emp%' 
ORDER BY 'Schema Name', 'table name'; 

Which have following output in SSMS plane

image

In above result set we can see our query had brought up all column name information @ table level and as well as  Field level such as Identity and Null operation

Hope this helps !!

Thanks for reading my blog !!

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 48 : How to Generate SELECT script for all tables in a Database

· BISQL # 49 : SQL Server Denali Feature # 1–FileTables #1 – Introduction,Theory

· BISQL # 50 : SQL Server Denali Feature # 1- FileTables # 2 – Demo , Scripting

· BISQL # 51 : SQL Server Denali Feature # 2 – Sequence number

· BISQL # 51 : SQL Server Denali Feature # 2 – Sequence number –All Explanation !!

 

Hope you will like this post on Finding of details of any column in Entire database in one query.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website

               For More information related to BI World visit my Mentalist Blog

                          Link Resource Blog >> Daily Interesting links

                         SQL Server Mentalist >> SQL Learning Blog

                         Business Intelligence Mentalist >> BI World

                                       Connect With me on

            | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                  Copyright © 2011 – 2012 Vishal Pawar

Advertisements
Categories: Query, SQL
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: