Can I SELECT *, NOT Col1 FROM Tblname; 1

UBfoolin · Dec 14, 2007

My table has a very large number of columns. Is there a simple way to select ALL of the fields in a query except one of them?

Something like:

SELECT *, NOT Col1 FROM Tblname;

Thanks in advance,
Dan

gmmastros · Dec 14, 2007

No

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

onpnt · Dec 14, 2007

That is just friggin hilarious. Thanks for the laugh UBfoolin

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

AlexCuse · Dec 14, 2007

You will thank yourself for years to come if you get out of the practice of using select * anyways...

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

SQLDenis · Dec 14, 2007

George, show him the trick

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

gmmastros · Dec 14, 2007

I don't wanna. You show it. I dare you!

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

gmmastros · Dec 14, 2007

And I'm not talking about the Query Analyzer 'drag the columns' thing. I dare you to show a Dynamic SQL method. [wink]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

SQLDenis · Dec 14, 2007

>>Dynamic SQL

what is that?, never heard of that?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

onpnt · Dec 14, 2007

ok sense I got a worthy star and all I think this will help you. It's just about the same thing to you as saying "*, NOT"

Code:

declare @tbl table (table_name varchar(255),column_name varchar(255),id int identity(1,1))
insert into @tbl 
select table_name, column_name from information_schema.[columns] Where Table_Name = 'Tblname'

declare @int int
declare @cols varchar(3000)
declare @cmd varchar(4000)

set @cols = ''
set @int = 1

while @int <= (select count(*) from information_schema.[columns] Where Table_Name = 'Tblname')
 begin
  if (select column_name from @tbl where id = @int) <> 'Col1'
   Begin
	set @cols = @cols + (select column_name from @tbl where id = @int) + ','
   End
  set @int = @int + 1
 end
 
If (Len(@cols) > 0)
set @cmd = 'Select ' + SubString(@cols,1,Len(@cols)-1) + ' From Tblname'

Exec(@cmd)

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

SQLDenis · Dec 14, 2007

Wow man, so that is dynamic SQL, I see I see
I will convert all my code to use dynamic SQL now, it seems so much easier.
I bet it also runs faster too

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

gmmastros · Dec 14, 2007

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

onpnt · Dec 14, 2007

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

AlexCuse · Dec 14, 2007

Code:

[COLOR=blue]use[/color] northwind


[COLOR=blue]declare[/color] @tablename [COLOR=blue]varchar[/color](100) [COLOR=green]--table name
[/color][COLOR=blue]declare[/color] @columnexclude [COLOR=blue]varchar[/color](1000)

[COLOR=blue]set[/color] @tablename = [COLOR=red]'customers'[/color]
[COLOR=blue]set[/color] @columnexclude = [COLOR=red]'phone, fax'[/color]


[COLOR=green]--this will hold all columns in table
[/color][COLOR=blue]declare[/color] @t [COLOR=blue]table[/color] (ID [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1), colname [COLOR=blue]varchar[/color](100))

[COLOR=green]--fill it with column list
[/color][COLOR=blue]insert[/color] @t
[COLOR=blue]select[/color] foo.name [COLOR=blue]from[/color] syscolumns foo
[COLOR=blue]inner[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] * [COLOR=blue]from[/color] sysobjects [COLOR=blue]where[/color] [COLOR=blue]name[/color] = @tablename and xtype = [COLOR=red]'U'[/color]) bar
[COLOR=blue]on[/color] foo.id = bar.id

[COLOR=green]--remove columns you don't want from list
[/color][COLOR=blue]delete[/color] @t 
[COLOR=blue]where[/color] colname in ([COLOR=blue]select[/color] [COLOR=blue]value[/color] [COLOR=blue]from[/color] test.dbo.Split(@columnexclude, [COLOR=red]','[/color]))

[COLOR=blue]declare[/color] @fooq nvarchar(4000)

[COLOR=blue]set[/color] @fooq = [COLOR=red]'select '[/color]

[COLOR=blue]declare[/color] @i [COLOR=blue]int[/color]
[COLOR=blue]set[/color] @i = 1

[COLOR=blue]while[/color] @i < ([COLOR=blue]select[/color] [COLOR=#FF00FF]max[/color](ID) [COLOR=blue]from[/color] @t)
[COLOR=blue]begin[/color]
	[COLOR=blue]select[/color] @fooq = @fooq + colname + [COLOR=red]','[/color]
		[COLOR=blue]from[/color] @t
		[COLOR=blue]where[/color] ID = @i

	[COLOR=blue]set[/color] @i = @i + 1
[COLOR=blue]end[/color]

[COLOR=blue]set[/color] @fooq = [COLOR=#FF00FF]left[/color](@fooq, len(@fooq) - 1) + [COLOR=red]' from customers'[/color]

[COLOR=blue]print[/color] @fooq

[COLOR=blue]execute[/color] sp_executesql @fooq

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

AlexCuse · Dec 14, 2007

Man, this onpnt guy is good!

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

onpnt · Dec 14, 2007

Yours is longer Alex. Much better!

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

ptheriault · Dec 14, 2007

I really impressed with how much sql stuff you guys know!!!
Have more purply things!

- Paul
- If at first you don't succeed, find out if the loser gets anything.

AlexCuse · Dec 14, 2007

PLEASE DO NOT USE THIS CODE. IT IS MEANT FOR DEMONSTRATION PURPOSES ONLY.

Here's a proc for any database

Code:

[COLOR=blue]create[/color] [COLOR=blue]proc[/color] fooProc(@tablename [COLOR=blue]varchar[/color](100), @columnexclude [COLOR=blue]varchar[/color](500))
 [COLOR=blue]as[/color]

[COLOR=green]--this will hold all columns in table
[/color][COLOR=blue]declare[/color] @t [COLOR=blue]table[/color] (ID [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1), colname [COLOR=blue]varchar[/color](100))

[COLOR=green]--fill it with column list
[/color][COLOR=blue]insert[/color] @t
[COLOR=blue]select[/color] foo.name [COLOR=blue]from[/color] syscolumns foo
[COLOR=blue]inner[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] * [COLOR=blue]from[/color] sysobjects [COLOR=blue]where[/color] [COLOR=blue]name[/color] = @tablename and xtype = [COLOR=red]'U'[/color]) bar
[COLOR=blue]on[/color] foo.id = bar.id

[COLOR=green]--remove columns you don't want from list
[/color][COLOR=blue]delete[/color] @t 
[COLOR=blue]where[/color] colname in ([COLOR=blue]select[/color] [COLOR=blue]value[/color] [COLOR=blue]from[/color] test.dbo.Split(@columnexclude, [COLOR=red]','[/color]))

[COLOR=green]--string to contain query
[/color][COLOR=blue]declare[/color] @fooq nvarchar(4000)
[COLOR=blue]set[/color] @fooq = [COLOR=red]'select '[/color]

[COLOR=green]--integer for while loop
[/color][COLOR=blue]declare[/color] @i [COLOR=blue]int[/color]
[COLOR=blue]set[/color] @i = 1

[COLOR=green]--while loop
[/color][COLOR=blue]while[/color] @i < ([COLOR=blue]select[/color] [COLOR=#FF00FF]max[/color](ID) [COLOR=blue]from[/color] @t)
[COLOR=blue]begin[/color]
	[COLOR=green]--add column name to query
[/color]	[COLOR=blue]select[/color] @fooq = @fooq + colname + [COLOR=red]','[/color]
		[COLOR=blue]from[/color] @t
		[COLOR=blue]where[/color] ID = @i
	[COLOR=green]--increment loop counter
[/color]	[COLOR=blue]set[/color] @i = @i + 1
[COLOR=blue]end[/color]

[COLOR=green]--trim the extra column and add table name to select from
[/color][COLOR=blue]set[/color] @fooq = [COLOR=#FF00FF]left[/color](@fooq, len(@fooq) - 1) + [COLOR=red]' from '[/color] + @tablename

[COLOR=green]--execute the constructed query
[/color][COLOR=blue]execute[/color] sp_executesql @fooq

PLEASE DO NOT USE THIS CODE. IT IS MEANT FOR DEMONSTRATION PURPOSES ONLY.

Now, honestly. Would you rather maintain a bunch of nonsense like this, or just type the column names?

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

SQLDenis · Dec 14, 2007

I detect a lot of unused space in nvarchar(4000)
you should pad it with comments

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

onpnt · Dec 14, 2007

>>I detect a lot of unused space in nvarchar(4000)
>>you should pad it with comments

nah, I heard there was a LTrim(RTrim( a few years back. Use it on every column just to make sure

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

SQLDenis · Dec 14, 2007

>>PLEASE DO NOT USE THIS CODE. IT IS MEANT FOR DEMONSTRATION PURPOSES ONLY.

how can you demonstrate without using it, please advice me
Also is that a set based solution, would that be a dynamic set?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Can I SELECT *, NOT Col1 FROM Tblname; 1

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

IS-IT--Management

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor