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 (http://blog.SQLAuthority.com)

About these ads

74 thoughts on “SQL SERVER – 2005 – Get Field Name and Type of Database Table

  1. @harinath

    select * from information_schema.columns

    This view will give you the list of tables and all the columns in those tables with some good information.

    Hope this helps,
    Imran.

  2. I cann’t start the SQL Server 05, anybody knows how to solve this error below?
    Thanks!!

    Logon failed for login due to trigger execution,
    Changed database context to ‘master’,
    Changed language setting tu us_english. MSSQL: (error 17892_

  3. Dear Sir,

    I want to get column name by sql command. There are 50 columns in my table and how to get column name to variable.

    And how to rename column by sql command. I tried to alter table command but it doesn’t work vb program.

    regards,

    sanjeewa

  4. Hi, how do you get the index information of one whole database?

    Like, if I want to get all the Index names, column name, column position in index and table name from the database Adventureworks?

  5. I have to compare two similar DB, one as SOURCE and the other as TARGET.
    I want to write single query to match the counts of all the individual columns present in SOURCE with that of TARGET.
    Can you pls help me out.

  6. Hello Abhishek,

    First I wrote a simple query which will return me all the table names with their rows count.

    I wrote this query two times, each time using a different database name, and I am then comparing the result of two queries.

    Your script starts here :
    Things to change in query.
    db_name1 – first database name
    db_name2 – second database name

    This query works only in SQL Server 2005, because Except key word works only in SQL Server 2005.

    select A.name, B.rowcnt
    from db_name1..sysobjects A, db_name1..sysindexes B
    where type = ‘u’ and B. indid < 2 and object_id(A.name) = B.id

    except

    select A.name, B.rowcnt
    from db_name2..sysobjects A, db_name2..sysindexes B
    where type = ‘u’ and B. indid < 2 and object_id(A.name) = B.id

    The above query will give you all the tables and rowcount that are present in database1 and are not present in database2.

    In order to get all the table names and rowcounts in database2 that are not present in database1 just reverse the query something like this.

    select A.name, B.rowcnt
    from db_name2..sysobjects A, db_name2..sysindexes B
    where type = ‘u’ and B. indid < 2 and object_id(A.name) = B.id

    except

    select A.name, B.rowcnt
    from db_name1..sysobjects A, db_name1..sysindexes B
    where type = ‘u’ and B. indid < 2 and object_id(A.name) = B.id

    When I compare tables… I also want to see sometimes what are the common tables present in both the database , at that time I will use something like this,

    select A.name, B.rowcnt
    from db_name2..sysobjects A, db_name2..sysindexes B
    where type = ‘u’ and B. indid < 2 and object_id(A.name) = B.id

    intersect

    select A.name, B.rowcnt
    from db_name1..sysobjects A, db_name1..sysindexes B
    where type = ‘u’ and B. indid < 2 and object_id(A.name) = B.id

    All these queries works only in SQL Server 2005.

    If you want to execute in SQL Server 2000.

    then you have to slightly change queries.. keeping the main part as it is…

    select A.name, B.rowcnt
    from db_name2..sysobjects A, db_name2..sysindexes B
    where type = ‘u’ and B. indid < 2 and object_id(A.name) = B.id

    with the use of exists in

    let me know if you want a script to run in SQL Server 2000.

    Hope this helps.
    Thanks,

    • can i get colunm names from the sql statement that I am using……
      e.g.
      SELECT (tbl_Employee.employee_FName+’ ‘+tbl_Employee.employee_LName) as Author_name, tbl_Forum.Forum_status,tbl_Forum.Forum_id,tbl_Forum.Forum_title,tbl_Forum.Forum_no_of_topics,tbl_Forum.Forum_ModifiedDate,(CASE tbl_Forum.Forum_Approved WHEN ‘Yes’ THEN ‘Approved’ ELSE ‘Not Approved’ END) as ApproveFlag From tbl_Forum LEFT OUTER JOIN tbl_Employee ON tbl_Employee.employee_userid=tbl_Forum.Forum_CreatedBy WHERE tbl_Forum.Forum_Approved=’Yes’

      SO I WANT Author_name,Forum_status,Forum_id…..ApproveFlag etc.
      in the return statement

  7. Sorry I should have been more specifc, index names, column names for each index, for a table (or tables). The following gives index names (and other info!) for tables:

    SELECT i.name AS index_name
    , CASE i.type_desc WHEN ‘CLUSTERED’ THEN ‘Yes’ ELSE ‘No’ END AS ‘Clustered’
    ,is_unique
    ,ds.type AS filegroup_or_partition_scheme
    ,ds.name AS filegroup_or_partition_scheme_name
    ,ignore_dup_key
    ,is_primary_key
    ,is_unique_constraint
    ,fill_factor
    ,is_padded
    ,is_disabled
    ,allow_row_locks
    ,allow_page_locks
    FROM sys.indexes AS i
    INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
    WHERE is_hypothetical = 0 AND i.index_id 0
    AND i.object_id IN (OBJECT_ID(”), OBJECT_ID(”))
    ORDER BY i.object_id;

  8. Hi All,

    I have a problem with my SSIS pkg, I am loading the data from excel sheet into SQL DB, while loading the excel sheet i am getting all null values in between , like i am getting 4 correct rows then couple of null rows, the pattern is continuing , my excel sheet is very cllean , i don understand where the problem lies,

    Any help would b appreciated,

    Thanks in advance

    Prashanth

  9. ADDITINAL DETAILS:

    My excel sheet has about 6000 rows, but when i insert it into SQL DB , i goes to 40k with all others being nulls in a random fashion .

  10. 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?

  11. 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

  12. 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 .

    • 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

  13. 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.

  14. 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

    • 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 .

      • 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

  15. 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

  16. 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

  17. 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………….

  18. 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 ………

  19. 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 = ?

  20. 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

  21. sir .. i have insert , select ,update ,delete query all r write one store procedure .. i will send table name and field value only …

    how to ??? please help me…..

    • @guna

      CREATE TABLE A(A VARCHAR(10));

      CREATE PROCEDURE INSERT_INTO(@Table_Name VARCHAR(MAX), @Column_Name VARCHAR(MAX), @Value VARCHAR(MAX))
      AS
      EXECUTE (‘INSERT INTO ‘ + @Table_Name + ‘(‘ + @Column_Name + ‘) VALUES(”’ + @Value + ”’)’);
      GO

      EXEC INSERT_INTO ‘A’, ‘A’, ‘AAA’;

      SELECT * FROM A;

      —————-

      Note, this is a bad idea for a number of reasons. Please explain why you want to do this so a better idea can be proposed.

  22. Hi All,

    I am trying to pull all of the column names from a table in sql 2005 into column ‘A’ of an excel worksheet using vba. I came across this thread and I used the syntax posted here:

    strSQL = “Select column_name from information_schema.columns where table_name=’mytblname’”

    This works fine, however, when I look at it in my excel worksheet, the first column name is not showing, instead it shows ‘column_name’. The vba code that I am using is below. It pulls the column names in, but then it errors out saying that the item cannot be found in the collection corresponding to the requested name or ordinal. I really would appreciate any helpful suggestions.

    strSQL = “Select column_name from information_schema.columns where table_name=’mytblname’”

    Set rsmywork = New ADODB.Recordset
    rsmywork.Open strSQL, connmywork, adOpenDynamic, adLockReadOnly, -1

    For intCount = 1 To rsmywork.Fields.Count
    Range(“A2″).Offset(intCount, 1).Value = rsmywork(intCount).OriginalValue
    Next

  23. I figured out why it was not showing the first column name. I have fixed my code, but not I am getting another error: item cannot be found in the collection corresponding to the requested name or ordinal.

    fixed code -

    For intCount = 0 To rsmywork.Fields.Count
    Range(“A2″).Offset(intCount, 0).Value = rsmywork.Fields(intCount)

  24. Hello all,
    I was querying google to try and find all tables and all their fields with all their data information inside of them and cam accross this web site. Unfortunatly this query was not excatly what I was looking for so here is how to get all information from each field in each table in YOUR database.

    select * from [YOUR_DATABASE].information_shcema.columns order by table_name

    This is for all who may still be confused just thought I would give out what I found.

    Be Well!

  25. I have a table `validationmaster` with a column called `vrformula`. It contains a formula like:

    pf > 1

    In that pf is one of the column names in the datasource table. I have to check whether pf of all the entries in the datasource table is > 1 or not, but I don’t know how to make it work.

    I can fetch that formula correctly but Sql Server considers that formula as a string, I don’t know how to change that whole expression into a formula.

    For example: `select * from datasource where meterid=4716 and pf>=1` is the statement I want to execute, with that formula at the end of the where clause being generated from the `vrformula` column.

    Thanks in advance

    • You need to use dynamic sql

      declare @f varchar(100), @sql varchar(8000)
      set @f=(select vformula from validationmaster where…)
      set @sql=’select * from datasource where meterid=4716 and ‘+@f)
      exec(@sql)

  26. i had two fields in my sql table.They are id and name.the data are 1,2,3,4,5,6 and x,x,x,y,y,z and the output is 26,24,30.And my question is what is the logic/relation between the data and output?

  27. hi all
    This is my first post on this web page and sadly its not a reply but a question

    I want to select only those columns from a table having content of columns is not null ,not by mentioning column name manually i want it dynamically.

  28. Hi everyone,

    Is there any way to add some details to the column of a table, so that we can store the datatypes used, the meaning of the datatype used, etc …

    For example, in a column [User_Type] of table [User], the values stored are as follows :
    0 – for user,
    1 – for employee,
    2 – for employer,
    3 – for admin … etc

    I just want to store the above information details, which is related to the column specified, in the table/database itself. So that it can be made more programming friendly …

  29. Hi All,
    Is it possible to get all the database names, their table names and their feilds names in a single query . if its possible then please share the query so that i can use it in my application.
    Thanks
    Aijaz

  30. For sql server 2008, for table name dbo.OPERATOR:

    select c.name, c.max_length, t.name from sys.columns c join sys.types t on c.system_type_id=t.system_type_id where c.object_id = object_id(‘OPERATOR’);

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