November 22, 2011 at 6:44 pm
Does anyone know offhand the SQL to list all the headers in a specified table?
Thank you!
November 22, 2011 at 7:47 pm
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
November 22, 2011 at 7:49 pm
Thanks for the reply. Looks good!
November 23, 2011 at 12:52 pm
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?
November 23, 2011 at 12:59 pm
Yes.
September 13, 2013 at 12:30 pm
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'
September 13, 2013 at 12:45 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 13, 2013 at 1:01 pm
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 ?
September 13, 2013 at 1:32 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2013 at 6:43 pm
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
October 1, 2013 at 7:21 am
jpnasab 81740 (9/30/2013)
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
Gosh that is a pretty limited function that doesn't help for this post at all. The OP is looking for the column names from a view.
And btw, you should not use the information_schema dmv's to view the schema. You should sys.objects.
http://technet.microsoft.com/en-us/library/ms188348.aspx
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy