Select query returning table design

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select query returning table design

Author

Topic

Spooky
Starting Member

3 Posts

Posted - 2004-09-22 : 03:09:55

Is it possible to return a set order of columns followed by a wildcard?

eg "select a,b,c,d,e,f, * from..."

The reason being, I have asp code that plugs into many variations of the same database and I need to return the design of the table to a string in asp. the first 'x' columns must match the expected input for asp design purposes.
As the end user may have reordered the database or added more columns, I need to return the schema in the correct expected order and with the 'custom' fields at the end of the array. Hence the "a,b,c,d, * "

If possible, Id prefer to do it just within the SQl statement, but any words of wisdom would be appreciated.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-22 : 03:14:36

yap possible.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 08:32:35

and this is how:

Declare @ColumnList varchar(1000)
SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'Orders'
select @ColumnList + ', *'

if that is not good wrap it in a function and you can then have it in one sql:
select dbo.GetColumnNames('tableName')

check out INFORMATION_SCHEMA.Columns view. it has a column Ordinal_position which determines an position of column in table.

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-22 : 08:51:50

Spirit, I don't think he really wants the *, he just is saying the first set is required, fill in the rest after that....


use pubs

Declare @ColumnList varchar(1000),
	@requiredCols varchar(1000)

Set @requiredCols = 'pub_id,country'

	Select @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name From 
	(
	SELECT top 100 percent column_name
	FROM INFORMATION_SCHEMA.Columns
	WHERE table_name = 'Publishers'
	Order By case when charindex(','+column_name+',',','+@requiredCols+',')=0 then 1000 else charindex(','+column_name+',',','+@requiredCols+',') end, column_name
	) A

	select @ColumnList

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources