SQL SERVER – 2005 – Get Field Name and Type of Database Table

In today’s article we will see question of one of reader Mohan and answer from expert Imran Mohammed. Imran thank you for answering question of Mohan.

Question of Mohan:

hi all,
how can i get field name and type etc. in MS-SQL server 2005. is there any query available???

Answer from Imran Mohammed:

@mohan

use database_name
Sp_help table_name

This stored procedure gives all the details of column, their types, any indexes, any constraints, any identity columns and some good information for that particular table.

Second method:

select column_name ‘Column Name’, data_type ‘Data Type’, character_maximum_length ‘Maximum Length’ from information_schema.columns where table_name = ‘table_name’

Hope this helps,
Imran.

Let see Imran’s example using sample database AdventureWorks.

USE AdventureWorks
GO
SELECT column_name 'Column Name',
data_type 'Data Type',
CHARacter_maximum_length 'Maximum Length'
FROM information_schema.columns
WHERE table_name = 'Address'
GO

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQLAuthority News – SQLAuthority Site With New Banner
Next Post
SQL SERVER – Query to Find Column From All Tables of Database

Related Posts

80 Comments. Leave new

  • In reference to Imran Mohammed example:

    USE AdventureWorks
    GO
    SELECT column_name ‘Column Name’,
    data_type ‘Data Type’,
    CHARacter_maximum_length ‘Maximum Length’
    FROM information_schema.columns
    WHERE table_name = ‘Address’

    Can you do the same thing using a temp table like #test?

    Reply
  • select * from tempdb.sys.columns where object_id =
    object_id(‘tempdb..#mytemptable’);

    Reply
  • sir,
    i want details of a table such as column name,data type,primary key,foreign key ,and foreign key column name
    i want this for documentation of college project
    in college it is called data dictionary

    please reply

    Reply
  • hello,
    I want to know a sql query!
    there is table name student contain columns like name, standard, roll, percentage.
    i want to see those column records only, in which the column name contains like ‘o’
    eg, here column name roll contains character ;o;
    so roll number column and its respective records to be shown .

    Reply
  • Hi,

    You are the answer to my prayers! Thanks for the query!

    Best Regards,
    Clarence D.

    Reply
    • Hi,

      how to parse the column name as a value that can be compared.
      Example:
      create table Test
      (columnname varchar(255))

      insert into Test
      values(zipcode)

      parameters: @InTable1 nvarchar(255),
      @InCol1 nvarchar(255)

      I have to join the tables by @InCol1 = test.columnname

      Reply
  • Dear Sir,

    Is there any way to add two clustered indexes to a table? I know there isn’t, but how would you imitate the same working
    then. Please suggest.

    Reply
  • Hello Ramlal,

    Can you explain what you want to acheive for that you need to imitate two clustered indexes on a table. If you can describe your requirements than we can provide a better solution.

    Regards
    Pinal Dave

    Reply
    • Thanks for you concern Sir, but I myself don’t know the exact requirement. Actually I was been asked the same by my senior DBA and I thought you could help me.

      Anyways its ok and thanks again .

      Reply
      • Tell your senior DBA that it is impossible. Clustered index defines how the actual data sorted. You can’t store data sorted in multiple ways obviously (you can fetch it ordered any way you like, of course). Unless you use some perverse scheme where you duplicate the whole table.

        MSDN conforms this: There can only be one clustered index per table, because the data rows themselves can only be sorted in one order

  • Very nice and thank u

    Reply
  • hello,
    I want to know a sql query!
    there is table name SystemParm contain columns like SystemParmID, ParmLabel, ParmTypeCode, OrderNumber,etc.
    i want to select only particular ColumNames from SystemParm table
    eg, here ColumnNames SystemParmID,ParmTypeCode
    i want select these ColumnNames

    Reply
  • Niket Chavan
    June 8, 2010 1:03 pm

    i want to fetch columns name but one by one in a loop which traverse from first column to last column for one particular row. please help me out…..

    Thanks
    Niket

    Reply
  • I want to search the columnsname but one by one in the asp.net

    Reply
  • I want to search the data .why get one data search and disply the data in the gridview.type a particular latter.to search the table.

    Reply
  • Sir plzz give me answer of my question……….

    Q:- I have table name “EMPLOYEE_LEAVE_RECORD”.This table have only 2 column name “ID” and “LEAVE_SESSION” and “ID” is int and “LEAVE_SESSION” is char(1) and table have only 2 record “ID” is 1 & 2 and “LEAVE_SESSION” is A & J. So my prblm is

    How can find “LEAVE_SESSION” value
    A as a April to March &
    J as s January to December by any query????????……….

    Plzzz….sir….i’ll wait your answer………….

    Reply
  • Q)may i modify user defind data type in sql server 2005.

    example:

    i created user defined datatype ‘std_name’ with varchar(30).

    it was used by diffrent tables.but now i want to modify my

    udt from varchar(30) to varchar(50).is it possible ………

    Reply
  • I don’t know for Microsoft access 2007.

    Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”).newInstance();
    con = DriverManager.getConnection(“jdbc:odbc:;DRIVER=Microsoft Access Driver (*.mdb);DBQ=D:\\lab5.mdb”,””,””);

    SELECT column_name,data_type,
    Character_maximum_length
    FROM xxxx
    WHERE table_name = ‘Address’;

    xxxx = ?

    Reply
  • Harikrishna Bura
    March 23, 2011 4:29 pm

    Yes the nice solution

    Thank you Pinal Dave

    Reply
  • Hi Pinal.

    Main Query is Can we get running totals for A Pivoted result.

    My scenario is like this.:

    I need to declare a dynamic table ,Let us say “tmp_Result” with dynamic number of columns

    Which will hold the data returned from

    a select statement(This is a pivoted result).

    For this i need to get the [SubDept1] datatype,[SubDept2] datatype,[SubDept3] datatype as string so that I can Create the table.

    And Then i need to insert a row with Totals for each column in the temp table

    The sample select statement as below:

    Select * from (
    select distinct City,SubDeptName,VAlue from tbl_Cities cl
    inner join
    [RelationTableWith Value Column] b
    on(cl.cityid=b.cityid)
    inner join tbl_SubDept g on(g.subdeptid=b.SubDeptId)
    )t PIVOT
    (max(Value) For Value in([SubDept1],[SubDept1],[SubDept1]))as pivottable

    Reply
  • hi all.,
    can we assign the Font-Names to table Column in sqlserver2005 please help

    Reply
  • LIke Arial,TLB-TTHemalatha,TimesNewRoman etc.,

    Reply
  • how we can get column name and corresponding column value(Row data) from the Table?
    Can u help me sir?

    Reply

Leave a Reply

Menu