Home List Column Names in a Table
Reply: 0

List Column Names in a Table

user6911
1#
user6911 Published in September 19, 2018, 11:02 am

I want to create code that will return all the column names in a table that I can use for other purposes. I have this code written.

DECLARE @SQLCommand VARCHAR(4000)
DECLARE @TableName VARCHAR(50)
SET @TableName = 'ship_to_ud'

SET @SQLCommand = 
'DECLARE @SelTable VARCHAR(50)
SET @SelTable = ' + '''' +  @TableName +  '''' + '
SELECT
     tables.name AS TableName
    ,all_columns.name AS ColumnName

FROM sys.all_columns
INNER JOIN sys.tables ON sys.all_columns.object_id = sys.tables.object_id
INNER JOIN sys.types ON sys.all_columns.user_type_id =                 
sys.types.user_type_id 
WHERE sys.tables.name = @SelTable
ORDER BY all_columns.name'

EXEC(@SQLCommand)

I would like to add a column that shows TableName.ColumnName, but the syntax defies me. Any help would be greatly appreciated. Thanks in advance.

share|improve this question
  • Just add ,tables.name + ''.'' + all_columns.name to the select list? Wrap the table/column name in quotename() if any require escaping. – Alex K. Apr 16 at 15:46
  • Oh so simple. I tried many harder things! This worked perfectly. – SQL-GBH Apr 16 at 15:55

3 Answers 3

active oldest votes
up vote 0 down vote
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.36436 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO