Query to Find Column Name From All Tables


Question : How many tables in your database have column name like  ‘CategoryID’  ?

Solution :

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%CategoryID%’
ORDER BY schema_name, table_name;

Question : How to find all the column name from your database ?

Solution :

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY schema_name, table_name;

Hope this will help

Jay Ganesh

Advertisements

5 thoughts on “Query to Find Column Name From All Tables

  1. Hi Mr.Sandeep,

    how to wright under give Sql Query in Linq ???

    select coalesce(Clientname,’,’) + ‘-‘ + coalesce(clienturl,’,’) as HostHeaders, ClientName, ClientURL, Id from Client

    1. Hi Krishna,

      The ?? operator is called the null-coalescing operator and is used to define a default value for a nullable value types as well as reference types.

      your Query can be written as :

      Var myClient = from c in DBContext.Client
      select new
      {
      HostHeaders = c.ClientName ?? ‘,’ + ‘-‘ + c.ClientUrl ?? ‘,’
      };

      Please check this out and let me know if you still face any issue.

      Regards,
      Sandeep

  2. Good day Mr. Sandeep

    I just want to know how will I be able to get all connecting columns of all tables through their column values? or is it even possible? hehehe…

    ‘Coz I am trying to connect my 2 tables to another table, and I think, I still need one more table to connect them for my report…

    Hope you understood my question… hehe

    thanks in advance! Blessings for you!

  3. The above query only considers system tables. It does not produce any information about the other tables. How do I change the query in order to do that?

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