Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL to list the headers in a table? Expand / Collapse
Author
Message
Posted Tuesday, November 22, 2011 6:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 29, 2012 11:59 PM
Points: 23, Visits: 28
Does anyone know offhand the SQL to list all the headers in a specified table?

Thank you!
Post #1210643
Posted Tuesday, November 22, 2011 7:47 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:16 PM
Points: 565, Visits: 319
This will list all tables and columns for a database. You can add a filter to return columns for a specified table.

SELECT st.name AS TableName
, sc.name AS ColumnName
FROM sys.tables AS st
INNER JOIN sys.columns sc ON st.OBJECT_ID = sc.OBJECT_ID
ORDER BY st.name
, sc.name

Post #1210647
Posted Tuesday, November 22, 2011 7:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 29, 2012 11:59 PM
Points: 23, Visits: 28
Thanks for the reply. Looks good!
Post #1210648
Posted Wednesday, November 23, 2011 12:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:56 AM
Points: 147, Visits: 661
For a single table:
select * from table where 1=2

Lists just the column names in the result panel. Is that what you meant by headers?
Post #1211304
Posted Wednesday, November 23, 2011 12:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 29, 2012 11:59 PM
Points: 23, Visits: 28
Yes.
Post #1211310
Posted Friday, September 13, 2013 12:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 5:11 PM
Points: 5, Visits: 20
How can I add a filter to that code ?

I've tried to with this :

where TABLE_NAME = 'v_SEC_NON_SEC_POSN_F_TOTAL'

Post #1494699
Posted Friday, September 13, 2013 12:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:36 PM
Points: 13,418, Visits: 12,283
joshcas (9/13/2013)
How can I add a filter to that code ?

I've tried to with this :

where TABLE_NAME = 'v_SEC_NON_SEC_POSN_F_TOTAL'



You need to reference the column not the alias.

st.name = 'v_SEC_NON_SEC_POSN_F_TOTAL'

My question though is that given the naming convention of "v_" I going to guess this is a view?. That means the above query will not find it.

You need to select from sys.objects instead of sys.tables.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1494700
Posted Friday, September 13, 2013 1:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 5:11 PM
Points: 5, Visits: 20
Hi Sean ,

Thank you so much for answering and for your solution, your solution saved me hours and headaches

You were correct, it was a view

Here is the code in case someone is looking for this

SELECT st.name AS TableName
, sc.name AS ColumnName
FROM sys.objects AS st
INNER JOIN sys.columns sc ON st.OBJECT_ID = sc.OBJECT_ID
WHERE st.name = 'v_SEC_NON_SEC_POSN_F_TOTAL'
OR st.name = 'AMID_MAPG'
ORDER BY st.name
, sc.name

One more thing, I’ve been trying to do the same thing but with an excel file using a SQL query (Powerpivot) should I open a new post for that question or should I use this one ?
Post #1494702
Posted Friday, September 13, 2013 1:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:36 PM
Points: 13,418, Visits: 12,283
You are welcome. Glad that worked for you.

Generally speaking you should always start your own thread. Your powerpivot is good example.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1494706
Posted Monday, September 30, 2013 6:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 6:33 PM
Points: 1, Visits: 1
Create function [dbo].[AF_TableColumns](@table_name nvarchar(55))
returns nvarchar(4000) as
begin
declare @str nvarchar(4000)
select @str = cast(rtrim(ltrim(column_name)) as nvarchar(500)) + coalesce(' ' + @str , ' ')
from information_schema.columns
where table_name = @table_name
group by table_name, column_name, ordinal_position
order by ordinal_position DESC
return @str
end

--select dbo.AF_TableColumns('YourTable') Select * from YourTable
Post #1500239
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse