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)

SQL Scripts, SQL System Table
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

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

    Reply
  • Sir , Insert Store Procedure … I will send Tablename and field value ….

    How to do? Please help me ..

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

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

    Reply
  • 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)

    Reply
  • Jeremy Andrijancic (@sillyhatman)
    January 7, 2012 12:21 am

    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!

    Reply
  • Hi All I want to fetch column names and its respective values from table.

    How can i do that?

    Reply
  • santhoshkumar
    April 17, 2012 8:47 am

    thank a lot …..

    Reply
  • How can this be done if you want to get the column names for a given view?

    Reply
  • How can I use this if I am looking for the column names in a view?

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

    Reply
    • 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)

      Reply
  • how to find out a code type of a column, which has multiple values in it as a drop down.

    Reply
  • 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?

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

    Reply
  • 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 …

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

    Reply
  • foo@bugmenot.com
    June 13, 2014 9:48 pm

    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’);

    Reply
  • can u send how to set primary key for the student information system project.

    Reply
  • how to retrieve the data from 3 unknown tables by using inner join if there is matching row, but we don’t know which one? means how to find matching or relationship between tables?

    Reply
  • i wish to fetch values from a table, and i wish to set a threshold/maximum value from which if the column value exceeds then it displays a message. I’m unable to do this so, kindly help me out.

    Reply
  • P.V.Rajagopal
    May 30, 2016 3:30 pm

    Hi Pinal
    CREATE TABLE testing
    (
    testing _id INT NOT NULL
    IDENTITY(1,1)
    ,name varchar (5)
    ,temp_name SYSNAME
    );

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

    I run the above query the output is

    output

    Column Name Data Type Maximum Length
    testing_id int NULL
    name varchar 5
    temp_name nvarchar 128

    In my question

    Q1 :
    In above table column_name is “temp_name” used datatype is sysname but output is display nvarchar and size is 128 Why?

    Q2 :
    How to get original datatype name? use only information_schema ?

    Reply

Leave a Reply