getting column names for table using query
-
Hi ,
I am using the following query to get the column names of a particular table.
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Categories'
The problem is it gives the column names in several rows. What I want is the have all the columns in a single row like
CategoryID CategoryName Description Picture
It is just the transpose of the result. Is there a way to do this in sql?
Thanks,
Sridhar!!
-
Try something like this:
Declare @Cols varchar(4096)
Set @Cols = ''
SELECT @Cols = @Cols + COLUMN_NAME + ' '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Categories'
Select RTrim(@Cols) As Columns
-
I think it's time for a shameless plug
IF Object_id('ListTableColumns') > 0
DROP FUNCTION ListTableColumns
GO
CREATE FUNCTION dbo.ListTableColumns (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + C.Name + ', '
FROMdbo.SysColumns C
WHEREC.id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
ORDER BYC.Name
SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))
RETURN @Items
END
GO
Select dbo.ListTableColumns(Object_id('SysObjects'))
--base_schema_ver, cache, category, crdate, deltrig, ftcatid, id, indexdel, info, instrig, name, parent_obj, refdate, replinfo, schema_ver, seltrig, stats_schema_ver, status, sysstat, type, uid, updtrig, userstat, version, xtype
DROP FUNCTION ListTableColumns
-
Thanks Remi Gregoire. The whole purpose of displaying it single rows is to copy it and paste in the excel. so I want each column name in a separate column so that I can copy and paste in the excel. But I don't know whether it is possible.
Thanks,
Sridhar!!
-
Would be easy to write client side. It would only take to while loops... And would be much simpler than any tsql method out there (even if you just split on the comma).
-
Sridhar if that is your sole reason use Query Analyser.
Use Tools=>Options=>results and Make results TAB delimited. Make Results Target to text.
Then Execute
SELECT * FROM TableName WHERE 1 = 2.
Copy Paste the results to EXCEL you have only the Columns in each cell harizontally.
You can copy paste the entire resultset like this.
Regards,
gova -
What about something like this?
declare @STR varchar(2000)
set @STR = ''
select @STR = @STR + '''' + c.name + ''' as ' + c.name + ','
from syscolumns c
where object_name(id) = 'Employees'
order by colid asc
set @STR = 'SELECT ' + LEFT(@str, LEN(@str)-1) + ' INTO #t SELECT * FROM #t DROP TABLE #t'
EXEC (@str)
Thanks
Greg
-
Nice trick
. -
No need to use dynamic sql here, no need for temp table either. Also, this doesn't work for all the tables in the same query and finally this solution has already been provided twice.
-
Thanks Remi for your position criticism. Kinda remind of Celko.
First of all, you are correct with the temp table. I use a similar method to auto create a table so that I can use it later. I just filled in column names for this thread. And, I didn't see above where someone selected the data back out. I see people creating comma separated lists, but when you select it, you get the results. If in Grid results, easy to cut/paste to Excel.
-
Sorry, didn't mean to rant on you
. -
Grids will be easy to cut and paste. One problem is you will not get column names as headings. Easy way is make TAB delimitted you get data and headings as needed.
Regards,
gova -
Thank you All
-
Govinn - I'm not able to recreate this (the QA trick...)
Also, could you please tell me what "where 1 = 2" does ?!?! Thanks!
**ASCII stupid question, get a stupid ANSI !!!** -
1=2 returns only the metadata, no select is actually done.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply