How to get column names of a given table.

• n.naveena

  Grasshopper

  Points: 13

  More actions

  October 11, 2008 at 12:32 pm

  #94069

  Hi 🙂 Im new to sql. This may be very simple.But, Please help me in doing this.

  I have a table Table1 which contains the name of other tables.

  Now i have to issue a query on Table1, to get the tablenames and make use of the tablename to retrieve that specified table's column names.

  Pleas help me with the SQL query for doing this..

• BradyB

  SSC Veteran

  Points: 241

  More actions

  October 11, 2008 at 12:42 pm

  #883792

  I use something like this

  -- Replace INSERT_TABLE_HERE with the table name you want information about.

  -- This query will give you the Column names, the respective datatype and

  -- position in the table

  SELECT COLUMN_NAME, DATA_TYPE , ORDINAL_POSITION

  FROM INFORMATION_SCHEMA.COLUMNS

  WHERE (TABLE_NAME = 'INSERT_TABLE_HERE')

• jacob sebastian

  SSChampion

  Points: 11822

  More actions

  October 12, 2008 at 10:17 pm

  #883967

  Another option:

  SELECT * FROM SYS.COLUMNS

  WHERE OBJECT_ID = OBJECT_ID('TableName')

  .

• MarlonRibunal

  Ten Centuries

  Points: 1332

  More actions

  October 13, 2008 at 1:12 pm

  #884312

  I have blogged about this. Please see my post at:

  http://dbalink.wordpress.com/2008/06/15/find-user-tables-and-their-columns-info-in-sql-server/

  SQL Server Database Administrator

• kodracon

  Ten Centuries

  Points: 1027

  More actions

  October 14, 2008 at 5:04 am

  #884527

  look at this post:http://www.sqlservercentral.com/scripts/Metadata/64603/

  or try this:

  SELECT syscolumns.name AS ColumnName,

  systypes.name AS Datatype,

  syscolumns.length AS Length

  FROM sysobjects,

  syscolumns,

  systypes

  WHERE sysobjects.id = syscolumns.id

  AND syscolumns.xtype = systypes.xtype

  AND sysobjects.name = @TableName

  or even better try this:

  DECLARE @TableName VARCHAR(50)

  SET @TableName = 'Campaign'

  DECLARE @AuthorName VARCHAR(50)

  SET @AuthorName = 'Chris Morton'

  DECLARE @CreatedDate VARCHAR(50)

  SET @CreatedDate = CONVERT(VARCHAR(50), GETDATE(), 111)

  DECLARE @SPName VARCHAR(100)

  DECLARE @SPDescription VARCHAR(1000)

  SET @SPDescription = '-- ============================================='

  + CHAR(10) + CHAR(13) + '-- AUTHOR: ' + @AuthorName + CHAR(10) + CHAR(13)

  + '-- CREATED DATE: ' + @CreatedDate + CHAR(10) + CHAR(13)

  + '-- =============================================' + CHAR(10) + CHAR(13)

  DECLARE @Statement VARCHAR(4000)

  DECLARE @SPText VARCHAR(8000)

  DECLARE @ParameterName VARCHAR(50)

  DECLARE @ParameterDataType VARCHAR(50)

  DECLARE @ParameterDataTypeLength VARCHAR(4)

  DECLARE @ParameterList VARCHAR(2000)

  DECLARE @SelectStatement VARCHAR(4000)

  SET @SelectStatement = 'SELECT'

  DECLARE @InsertStatement VARCHAR(4000)

  SET @InsertStatement = 'INSERT INTO'

  DECLARE @InsertStatementValues VARCHAR(2000)

  DECLARE @TableMetaData AS TABLE

  (

  ID INT IDENTITY(1, 1),

  ColumnName VARCHAR(50) NOT NULL,

  DataType VARCHAR(50) NOT NULL,

  Length VARCHAR(4) NOT NULL

  )

  INSERT INTO @TableMetaData

  (

  ColumnName,

  DataType,

  Length

  )

  SELECT syscolumns.name AS ColumnName,

  systypes.name AS Datatype,

  syscolumns.length AS Length

  FROM sysobjects,

  syscolumns,

  systypes

  WHERE sysobjects.id = syscolumns.id

  AND syscolumns.xtype = systypes.xtype

  AND sysobjects.name = @TableName

  DECLARE @NumColumns INT

  SET @NumColumns = ( SELECT COUNT(1)

  FROM @TableMetaData

  )

  DECLARE @ColumnName VARCHAR(50)

  DECLARE @Counter INT

  SET @Counter = 1

  --select statement

  SET @SPName = 'CREATE PROCEDURE select' + @TableName + CHAR(10) + CHAR(13)

  WHILE @Counter <= @NumColumns

  BEGIN

  SET @ColumnName = ( SELECT ColumnName

  FROM @TableMetaData

  WHERE ID = @Counter

  )

  IF @Counter = @NumColumns

  BEGIN

  SET @SelectStatement = @SelectStatement + CHAR(10) + CHAR(13)

  + ' [' + @ColumnName + ']' + +CHAR(10) + CHAR(13)

  + 'FROM ' + CHAR(10) + CHAR(13) + @TableName

  END

  ELSE

  BEGIN

  SET @SelectStatement = @SelectStatement + CHAR(10) + CHAR(13)

  + ' [' + @ColumnName + '],'

  END

  SET @Counter = @Counter + 1

  END

  SET @Statement = @SelectStatement

  SET @SPText = @SPDescription + @SPName + ISNULL(@ParameterList,

  CHAR(10) + CHAR(13)) + 'AS'

  + CHAR(10) + CHAR(13) + 'BEGIN' + CHAR(10) + CHAR(13) + @Statement

  + CHAR(10) + CHAR(13) + 'END'

  SELECT @SPText AS SelectStatement

  --INSERT STATEMENT

  SET @SPName = 'CREATE PROCEDURE insert' + @TableName + CHAR(10) + CHAR(13)

  SET @Counter = 1

  WHILE @Counter <= @NumColumns

  BEGIN

  SET @ColumnName = ( SELECT ColumnName

  FROM @TableMetaData

  WHERE ID = @Counter

  )

  SET @ParameterDataType = ( SELECT DataType

  + ISNULL(CASE LOWER(Datatype)

  WHEN 'varchar'

  THEN '(' + Length + ')'

  WHEN 'char'

  THEN '(' + Length + ')'

  WHEN 'varbinary'

  THEN '(' + Length + ')'

  WHEN 'binary'

  THEN '(' + Length + ')'

  WHEN 'nchar'

  THEN '(' + Length + ')'

  WHEN 'nvarchar'

  THEN '(' + Length + ')'

  WHEN 'decimal'

  THEN '(' + Length + ')'

  END, '')

  FROM @TableMetaData

  WHERE ID = @Counter

  )

  SET @ParameterName = '@' + @ColumnName

  IF @Counter = 1

  BEGIN

  SET @InsertStatement = @InsertStatement + ' ' + @TableName

  + CHAR(10) + CHAR(13) + ' ([' + @ColumnName + '],'

  + CHAR(10) + CHAR(13)

  SET @InsertStatementValues = @ParameterName + ',' + CHAR(10)

  + CHAR(13)

  SET @ParameterList = @ParameterName + ' ' + @ParameterDataType

  + ', ' + CHAR(10) + CHAR(13)

  END

  IF @Counter > 1

  AND @Counter < @NumColumns - 1

  BEGIN

  SET @InsertStatement = @InsertStatement + ' [' + @ColumnName

  + '],' + CHAR(10) + CHAR(13)

  SET @InsertStatementValues = @InsertStatementValues + ' '

  + @ParameterName + ',' + CHAR(10) + CHAR(13)

  SET @ParameterList = @ParameterList + @ParameterName + ' '

  + @ParameterDataType + ',' + CHAR(10) + CHAR(13)

  END

  IF @Counter = @NumColumns

  BEGIN

  SET @InsertStatement = @InsertStatement + '[' + @ColumnName

  + '])' + CHAR(10) + CHAR(13) + 'VALUES' + CHAR(10)

  + CHAR(13) + '('

  SET @InsertStatementValues = @InsertStatementValues + ' '

  + @ParameterName + ')'

  SET @ParameterList = @ParameterList + @ParameterName + ' '

  + @ParameterDataType

  END

  SET @Counter = @Counter + 1

  END

  SET @Statement = @InsertStatement + @InsertStatementValues

  --construct text

  SET @SPText = @SPDescription + @SPName + ISNULL(@ParameterList,

  CHAR(10) + CHAR(13)) + 'AS'

  + CHAR(10) + CHAR(13) + 'BEGIN' + CHAR(10) + CHAR(13) + @Statement

  + CHAR(10) + CHAR(13) + 'END'

  SELECT @SPText AS insertStatement

  --update statement

  DECLARE @ColumnParameter VARCHAR(4000)

  SET @ColumnParameter = ''

  SET @SPName = 'CREATE PROCEDURE update' + @TableName + CHAR(10) + CHAR(13)

  DECLARE @UpdateStatement VARCHAR(8000)

  SET @UpdateStatement = 'UPDATE ' + @TableName + CHAR(10) + CHAR(13) + 'SET '

  SET @Counter = 1

  WHILE @Counter <= @NumColumns

  BEGIN

  SET @ColumnName = ( SELECT ColumnName

  FROM @TableMetaData

  WHERE ID = @Counter

  )

  SET @ParameterName = '@' + @ColumnName

  SET @ParameterDataType = ( SELECT DataType

  + ISNULL(CASE LOWER(Datatype)

  WHEN 'varchar'

  THEN '(' + Length + ')'

  WHEN 'char'

  THEN '(' + Length + ')'

  WHEN 'varbinary'

  THEN '(' + Length + ')'

  WHEN 'binary'

  THEN '(' + Length + ')'

  WHEN 'nchar'

  THEN '(' + Length + ')'

  WHEN 'nvarchar'

  THEN '(' + Length + ')'

  WHEN 'decimal'

  THEN '(' + Length + ')'

  END, '')

  FROM @TableMetaData

  WHERE ID = @Counter

  )

  IF @Counter = 1

  BEGIN

  SET @ParameterList = @ParameterName + ' ' + @ParameterDataType

  + ', ' + CHAR(10) + CHAR(13)

  END

  IF @Counter = @NumColumns

  BEGIN

  SET @ColumnParameter = @ColumnParameter + @ColumnName + ' = '

  + @ParameterName + CHAR(10) + CHAR(13)

  SET @ParameterList = @ParameterList + @ParameterName + ' '

  + @ParameterDataType + CHAR(10) + CHAR(13)

  END

  IF @Counter > 1

  AND @Counter < @NumColumns - 1

  BEGIN

  SET @ColumnParameter = @ColumnParameter + @ColumnName + ' = '

  + @ParameterName + ',' + CHAR(10) + CHAR(13)

  SET @ParameterList = @ParameterList + @ParameterName + ' '

  + @ParameterDataType + ',' + CHAR(10) + CHAR(13)

  END

  SET @Counter = @Counter + 1

  END

  SET @Statement = @UpdateStatement + @ColumnParameter

  SET @SPText = @SPDescription + @SPName + ISNULL(@ParameterList,

  CHAR(10) + CHAR(13)) + 'AS'

  + CHAR(10) + CHAR(13) + 'BEGIN' + CHAR(10) + CHAR(13) + @Statement

  + CHAR(10) + CHAR(13) + 'END'

  SELECT @SPText AS insertStatement

• Madhivanan-208264

  SSCertifiable

  Points: 7516

  More actions

  October 14, 2008 at 7:25 am

  #884599

  Also

  EXEC sp_columns 'table_name'

  ---

  Madhivanan

  Failing to plan is Planning to fail

• Rudyx - the Doctor

  SSC-Forever

  Points: 43695

  More actions

  October 14, 2008 at 10:48 am

  #884772

  ... and also ...

  exec sp_help 'Tablename'

  RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

• David Korb

  SSCrazy

  Points: 2505

  More actions

  October 14, 2008 at 6:17 pm

  #884969

  rudy komacsar (10/14/2008)

  ---

  ... and also ...

  exec sp_help 'Tablename'

  You can also select the table name and hit alt-F1 which is the shortcut for the code above.

• Madhivanan-208264

  SSCertifiable

  Points: 7516

  More actions

  October 15, 2008 at 4:06 am

  #885103

  SprocKing (10/14/2008)

  ---

  rudy komacsar (10/14/2008)

  ---

  ... and also ...

  exec sp_help 'Tablename'

  You can also select the table name and hit alt-F1 which is the shortcut for the code above.

  Here are many possible methods

  http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/28/different-ways-to-know-structure-of-a-table.aspx

  ---

  Madhivanan

  Failing to plan is Planning to fail

• ck9663

  Right there with Babe

  Points: 763

  More actions

  October 16, 2008 at 3:58 am

  #885701

  Will this work?

  select t.name, c.name

  from sys.tables t

  inner join YourTable1 y on y.YourColumnWithTableName = t.name

  inner join sys.columns c on t.object_id = c.object_id

  order by t.name

  Happy coding!

  -- CK

• Kishore.P

  SSCrazy Eights

  Points: 8147

  More actions

  October 16, 2008 at 4:11 am

  #885706

  get the column name from this INFORMATION_SCHEMA.COLUMNS

• walter_r

  Newbie

  Points: 3

  More actions

  October 16, 2008 at 7:31 am

  #885811

  :w00t:

  Excellent job,

  You leave me with the mount open

  I'm a .Net programmer , I Needed it something like this,

  Tanks, really appreciate

• emailtoDeepa

  SSC Enthusiast

  Points: 164

  More actions

  October 17, 2008 at 8:26 am

  #886392

  Declare @nameoftable varchar(50)

  set @nameoftable = 'your Table here'

  Select *

  from Sys.Columns C Inner join Sys.objects O

  C.object_id = O.object_id

  where O.name = @nameoftable)

• aramya21

  Newbie

  Points: 7

  More actions

  October 19, 2008 at 12:06 am

  #886840

  alter table ;:cool: