Number of Columns

How can I tell how many column is returned in a query like this?
Select *.general, lname.newbusiness, contact.newbusiness, ….
ThanksThere is no built-in functions in SQL Server which does this. However, most
client side data access APIs will have a mechanism of identifying the number
of columns in the columns collection of the resultset.
Anith|||Depends how you are returning the data. For example, the Count porperty
of the ADO Fields collection gives this information.
Anyway, it is generally considered bad practice to use SELECT * in a
production application. List all the column names individually. Query
Analyzer lets you click and drag the column list into the editing
window so you don't have to do lots of typing.
--
David Portas
SQL Server MVP
--|||Actually, I don't really know that syntax, but I guess you mean something
similar to this :
select table1.*, table2.some_field, table2.some_otherfield, table3.*
etc
The easiest way I can see is by actually counting the fields in the
resultset. If you do not want the results than add a WHERE 1 = 2 to the end,
that way you will get the structure of the resultset, but without any data
in it (and without having to wait for the server to do all the work)
A rather complex route to find out upfront would be to do it like this :
select total_number_of_columns = (SELECT COUNT(*) FROM syscolumns col JOIN
sysobjects obj ON obj.id = col.id and obj.name = 'table1' and xtype =
'U') -- table 1 : * = all columns
+ 2 -- table 2 only two
columns asked for
+ (SELECT COUNT(*) FROM
syscolumns col JOIN sysobjects obj ON obj.id = col.id and obj.name =
'table3' and xtype = 'U') -- table 3 : * = all columns
Probably works, but I wonder what it's use is.
Cu
Roby
"Emma" <Emma@.discussions.microsoft.com> wrote in message
news:678A98EB-AC14-4C4B-B3F0-9D81D837B24F@.microsoft.com...
> How can I tell how many column is returned in a query like this?
> Select *.general, lname.newbusiness, contact.newbusiness, ..
> Thanks
>|||Emma,
Not sure what you are trying to accomplish, but here is one way to do it in
t-sql:
Select 1 as '1', 2 as '2', 3 as '3', 4 as '4', 5 as '5', 6 as '6'
into ##p
select count(*) from tempdb..syscolumns where id = object_id('tempdb..##p')
Ilya
"Emma" <Emma@.discussions.microsoft.com> wrote in message
news:678A98EB-AC14-4C4B-B3F0-9D81D837B24F@.microsoft.com...
> How can I tell how many column is returned in a query like this?
> Select *.general, lname.newbusiness, contact.newbusiness, ..
> Thanks
>|||This won't work in every case. SELECT INTO requires that the column
names are unique so if you join two tables and don't alias the columns
then it may fail. Perhaps the OP knows that her two tables won't have
conflicting column names but if the columns were really fixed and known
in advance then she wouldn't need a query to count them. If all the
columns are aliased then they are presumably known and therefore the
query is still pretty pointless. I guess the real question here is
exactly why the OP wouldn't know at development time how many columns
would be returned by her queries.
--
David Portas
SQL Server MVP
--|||Since you havent provided the full query, I'll try to help you with
what you had given me.
Run the following in Qeary Analyzer.
SP_HELP general
find the number of columns and then add each columns that followes
afterwords...
NOTE:
It will help if people post questions with proper information and Code
and be clear on what they are looking to solve!!!!!!!!!!!!!!!!!|||Since you havent provided the full query, I'll try to help you with
what you had given me.
Run the following in Qeary Analyzer.
SP_HELP general
find the number of columns and then add each columns that followes
afterwords...
NOTE:
It will help if people post questions with proper information and Code
and be clear on what they are looking to solve!!!!!!!!!!!!!!!!!|||Since you havent provided the full query, I'll try to help you with
what you had given me.
Run the following in Qeary Analyzer.
SP_HELP general
find the number of columns and then add each columns that followes
afterwords...
NOTE:
It will help if people post questions with proper information and Code
and be clear on what they are looking to solve!!!!!!!!!!!!!!!!!