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
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
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
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
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
Copyright © 2011 – 2012 Vishal Pawar
SQL Server Mentalist 