June 15, 2005 at 3:35 pm
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!!
June 15, 2005 at 6:36 pm
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
June 16, 2005 at 8:24 am
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
June 16, 2005 at 9:44 am
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!!
June 16, 2005 at 9:59 am
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).
June 16, 2005 at 10:04 am
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
June 16, 2005 at 10:11 am
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
June 16, 2005 at 10:16 am
Nice trick .
June 16, 2005 at 10:17 am
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.
June 16, 2005 at 10:28 am
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.
June 16, 2005 at 10:32 am
Sorry, didn't mean to rant on you .
June 16, 2005 at 10:35 am
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
June 16, 2005 at 11:35 am
Thank you All
June 16, 2005 at 1:14 pm
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 !!!**
June 16, 2005 at 1:16 pm
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