|
Home > Archive > MS SQL Server > March 2006 > Select all columns except certain types
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Select all columns except certain types
|
|
| neilsanner@yahoo.com 2006-03-10, 8:23 pm |
| Hi,
Hi have a table with over 90 columns. I would like to create a query
that would select all columns except those with the date data type.
Any ideas?
neilsanner
| |
| James Ma 2006-03-10, 8:23 pm |
| You can use a INFORMATION_SCHEMA view to help generate the column list.
James
"neilsanner@yahoo.com" wrote:
> Hi,
>
> Hi have a table with over 90 columns. I would like to create a query
> that would select all columns except those with the date data type.
>
> Any ideas?
>
> neilsanner
>
>
| |
|
| rebuild your app to have tables with less then 20 columns.
| |
| neilsanner@yahoo.com 2006-03-13, 11:23 am |
| Thanks for the idea.
Anyways, is there an existing method to write a query that only selects
columns with text data type? Or doubles? Or Booleans? Or any other data
type?
neilsanner
Doug wrote:
> rebuild your app to have tables with less then 20 columns.
| |
| David Portas 2006-03-13, 11:23 am |
| neilsanner@yahoo.com wrote:
> Hi,
>
> Hi have a table with over 90 columns. I would like to create a query
> that would select all columns except those with the date data type.
>
> Any ideas?
>
> neilsanner
Just list the required columns in your SELECT statement. Query Analyzer
or Management Studio will automatically generate the full column list
for you. I guess it might take 2 minutes to delete the columns you
don't want. Much less if you have a good naming convention for dates.
The number of your columns and the nature of your requirement suggests
that you may have a design issue that you ought to fix. I could be
wrong of course. Just a guess.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
| |
|
| one of the system tables has a listing of all the columns in the table.
I'm thinking it is a join of sysobjects, syscolumns, and another. i've
got code somewhere that did all this, but can't remember where it was.
Then, once you hae the column names, dynamic SQL.
this si a great brain teaser, but for a one time shot, much easier to
just fix the tables.
| |
| neilsanner@yahoo.com 2006-03-27, 9:23 am |
| Thanks everyone for the input.
I finally decided to create the sql query dynamically. I'll use a
script to check every columns' type and build the sql query
accordingly.
neilsanner
Doug wrote:
> one of the system tables has a listing of all the columns in the table.
>
> I'm thinking it is a join of sysobjects, syscolumns, and another. i've
> got code somewhere that did all this, but can't remember where it was.
>
> Then, once you hae the column names, dynamic SQL.
>
> this si a great brain teaser, but for a one time shot, much easier to
> just fix the tables.
|
|
|
|
|