Column Headings in Result Sets

Does the ANSI SQL Standard specify how columns are named in result sets?

I ask this because I just came across some behavior that surprised me, or went against my expectations.

Given the table: t1 (c1 int)
and this query: select t1.c1 from t1

the results are displayed as follows on all databases I tried it on (SQL Server, Oracle, DB2, mySQL and Sybase):

c1
--
1
2
..etc

I would have expected the column heading to be different, as follows:

t1.c1
--
1
2
...etc

So, if you say 't1.c1' in the select list, you should see t1.c1 in the column heading in the result set.

Given another table, t2 (c1 int), the column headings in the result set of the query 'select t1.c1, t2.c1 from t1,t2' were the same on all database platforms:
c1 | c1
----
1 | 2
2 | 3

This strikes me as ambiguous, because how do you know which result column came from which table ? I know that you can use column aliases if you want unique names in your column headings, but is there a good reason why the column headings 't1.c1' and 't2.c1' wouldn't be used by default if you specify 't1.c1' and 't2.c1' as items in your select list?

Thanks,
Colm.... is there a good reason why the column headings 't1.c1' and 't2.c1' wouldn't be used by default if you specify 't1.c1' and 't2.c1' as items in your select list?yes -- because by the time the result set is constructed, the database has forgotten which table each column came from

:)

more accurately, a column name is an identifier, whereas "t1.c1" is a string

you could always do this, if you really need it --

select t1.c1 as "t1.c1", ...

collecting db statistics

Hi;
I came across this article in SQL Server Magazine (March2003),under Letters
section, pg7, but I don't understand how to go abt implement or use the tip:
" schedule a wrapper stored procedure that executs a procedure in master for
each application database, such as;
EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
This SP will apparently get info on DB growth, data-file, table stats, which
is what I want to get.
er....can someone show me the way what I need to do ?
tks much
I would suggest that you contact ed.parr@.royalmail.com to obtain the source
code for this procedure. Basically what you found was a letter explaining
how if my store procedure, which is described in SQL Server magazine article
Avoiding the Redzone December 2002 was place in master it would simplify my
code. I don't think Ed Parr has ever shared the code for his sp he was
referring to in his letter to SQL Server Magazine.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> Hi;
> I came across this article in SQL Server Magazine (March2003),under
Letters
> section, pg7, but I don't understand how to go abt implement or use the
tip:
> " schedule a wrapper stored procedure that executs a procedure in master
for
> each application database, such as;
> EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
> This SP will apparently get info on DB growth, data-file, table stats,
which
> is what I want to get.
> er....can someone show me the way what I need to do ?
>
> tks much
>
|||Also I should have said, I think if you go to InstantDoc ID 26874 you might
be able to down load my code for gathering space statistics, but I think you
might need to be a subscriber to actually read the article that explains the
process. If you go this route let me know if you have any questions about
the sp, and table, and I will be glad to answer them.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:O$hBoPzbEHA.3944@.tk2msftngp13.phx.gbl...
> I would suggest that you contact ed.parr@.royalmail.com to obtain the
source
> code for this procedure. Basically what you found was a letter explaining
> how if my store procedure, which is described in SQL Server magazine
article
> Avoiding the Redzone December 2002 was place in master it would simplify
my
> code. I don't think Ed Parr has ever shared the code for his sp he was
> referring to in his letter to SQL Server Magazine.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "pohkeat" <pohkeat@.hotmail.com> wrote in message
> news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> Letters
> tip:
> for
> which
>
|||hi;
tks for the tip. Got my hands on the code,with it I've created
usp_get_dbstats in my user db, and also DBSTATS in the user db.
how do i execu it ? I ran usp_get_dbstats in QA, copy the results and pasted
in another QA window, ran it, DBCC printed no error for a couple of lines
until at the end, it printed Server: Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'Procedure:'.
I opened the DBSTATS table, and its empty.
where did I go wrong ?
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eipLySzbEHA.3988@.tk2msftngp13.phx.gbl...
> Also I should have said, I think if you go to InstantDoc ID 26874 you
might
> be able to down load my code for gathering space statistics, but I think
you
> might need to be a subscriber to actually read the article that explains
the
> process. If you go this route let me know if you have any questions
about
> the sp, and table, and I will be glad to answer them.
> --
> ----
--
> ----
--[vbcol=seagreen]
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:O$hBoPzbEHA.3944@.tk2msftngp13.phx.gbl...
> source
explaining[vbcol=seagreen]
> article
> my
> ----
> --
> ----
> --
the[vbcol=seagreen]
master
>

collecting db statistics

Hi;
I came across this article in SQL Server Magazine (March2003),under Letters
section, pg7, but I don't understand how to go abt implement or use the tip:
" schedule a wrapper stored procedure that executs a procedure in master for
each application database, such as;
EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
This SP will apparently get info on DB growth, data-file, table stats, which
is what I want to get.
er....can someone show me the way what I need to do ?
tks muchI would suggest that you contact ed.parr@.royalmail.com to obtain the source
code for this procedure. Basically what you found was a letter explaining
how if my store procedure, which is described in SQL Server magazine article
Avoiding the Redzone December 2002 was place in master it would simplify my
code. I don't think Ed Parr has ever shared the code for his sp he was
referring to in his letter to SQL Server Magazine.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> Hi;
> I came across this article in SQL Server Magazine (March2003),under
Letters
> section, pg7, but I don't understand how to go abt implement or use the
tip:
> " schedule a wrapper stored procedure that executs a procedure in master
for
> each application database, such as;
> EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
> This SP will apparently get info on DB growth, data-file, table stats,
which
> is what I want to get.
> er....can someone show me the way what I need to do ?
>
> tks much
>|||Also I should have said, I think if you go to InstantDoc ID 26874 you might
be able to down load my code for gathering space statistics, but I think you
might need to be a subscriber to actually read the article that explains the
process. If you go this route let me know if you have any questions about
the sp, and table, and I will be glad to answer them.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:O$hBoPzbEHA.3944@.tk2msftngp13.phx.gbl...
> I would suggest that you contact ed.parr@.royalmail.com to obtain the
source
> code for this procedure. Basically what you found was a letter explaining
> how if my store procedure, which is described in SQL Server magazine
article
> Avoiding the Redzone December 2002 was place in master it would simplify
my
> code. I don't think Ed Parr has ever shared the code for his sp he was
> referring to in his letter to SQL Server Magazine.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "pohkeat" <pohkeat@.hotmail.com> wrote in message
> news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> Letters
> tip:
> for
> which
>|||hi;
tks for the tip. Got my hands on the code,with it I've created
usp_get_dbstats in my user db, and also DBSTATS in the user db.
how do i execu it ? I ran usp_get_dbstats in QA, copy the results and pasted
in another QA window, ran it, DBCC printed no error for a couple of lines
until at the end, it printed Server: Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'Procedure:'.
I opened the DBSTATS table, and its empty.
where did I go wrong ?
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eipLySzbEHA.3988@.tk2msftngp13.phx.gbl...
> Also I should have said, I think if you go to InstantDoc ID 26874 you
might
> be able to down load my code for gathering space statistics, but I think
you
> might need to be a subscriber to actually read the article that explains
the
> process. If you go this route let me know if you have any questions
about
> the sp, and table, and I will be glad to answer them.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:O$hBoPzbEHA.3944@.tk2msftngp13.phx.gbl...
> source
explaining[vbcol=seagreen]
> article
> my
> ----
> --
> ----
> --
the[vbcol=seagreen]
master[vbcol=seagreen]
>

collecting db statistics

Hi;
I came across this article in SQL Server Magazine (March2003),under Letters
section, pg7, but I don't understand how to go abt implement or use the tip:
" schedule a wrapper stored procedure that executs a procedure in master for
each application database, such as;
EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
This SP will apparently get info on DB growth, data-file, table stats, which
is what I want to get.
er....can someone show me the way what I need to do ?
tks muchI would suggest that you contact ed.parr@.royalmail.com to obtain the source
code for this procedure. Basically what you found was a letter explaining
how if my store procedure, which is described in SQL Server magazine article
Avoiding the Redzone December 2002 was place in master it would simplify my
code. I don't think Ed Parr has ever shared the code for his sp he was
referring to in his letter to SQL Server Magazine.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> Hi;
> I came across this article in SQL Server Magazine (March2003),under
Letters
> section, pg7, but I don't understand how to go abt implement or use the
tip:
> " schedule a wrapper stored procedure that executs a procedure in master
for
> each application database, such as;
> EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
> This SP will apparently get info on DB growth, data-file, table stats,
which
> is what I want to get.
> er....can someone show me the way what I need to do ?
>
> tks much
>|||Also I should have said, I think if you go to InstantDoc ID 26874 you might
be able to down load my code for gathering space statistics, but I think you
might need to be a subscriber to actually read the article that explains the
process. If you go this route let me know if you have any questions about
the sp, and table, and I will be glad to answer them.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:O$hBoPzbEHA.3944@.tk2msftngp13.phx.gbl...
> I would suggest that you contact ed.parr@.royalmail.com to obtain the
source
> code for this procedure. Basically what you found was a letter explaining
> how if my store procedure, which is described in SQL Server magazine
article
> Avoiding the Redzone December 2002 was place in master it would simplify
my
> code. I don't think Ed Parr has ever shared the code for his sp he was
> referring to in his letter to SQL Server Magazine.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "pohkeat" <pohkeat@.hotmail.com> wrote in message
> news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> > Hi;
> >
> > I came across this article in SQL Server Magazine (March2003),under
> Letters
> > section, pg7, but I don't understand how to go abt implement or use the
> tip:
> >
> > " schedule a wrapper stored procedure that executs a procedure in master
> for
> > each application database, such as;
> >
> > EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
> >
> > This SP will apparently get info on DB growth, data-file, table stats,
> which
> > is what I want to get.
> >
> > er....can someone show me the way what I need to do ?
> >
> >
> > tks much
> >
> >
>|||hi;
tks for the tip. Got my hands on the code,with it I've created
usp_get_dbstats in my user db, and also DBSTATS in the user db.
how do i execu it ? I ran usp_get_dbstats in QA, copy the results and pasted
in another QA window, ran it, DBCC printed no error for a couple of lines
until at the end, it printed Server: Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'Procedure:'.
I opened the DBSTATS table, and its empty.
where did I go wrong ?
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eipLySzbEHA.3988@.tk2msftngp13.phx.gbl...
> Also I should have said, I think if you go to InstantDoc ID 26874 you
might
> be able to down load my code for gathering space statistics, but I think
you
> might need to be a subscriber to actually read the article that explains
the
> process. If you go this route let me know if you have any questions
about
> the sp, and table, and I will be glad to answer them.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:O$hBoPzbEHA.3944@.tk2msftngp13.phx.gbl...
> > I would suggest that you contact ed.parr@.royalmail.com to obtain the
> source
> > code for this procedure. Basically what you found was a letter
explaining
> > how if my store procedure, which is described in SQL Server magazine
> article
> > Avoiding the Redzone December 2002 was place in master it would simplify
> my
> > code. I don't think Ed Parr has ever shared the code for his sp he was
> > referring to in his letter to SQL Server Magazine.
> >
> > --
> >
> ----
> --
> ----
> --
> > --
> >
> > Need SQL Server Examples check out my website at
> > http://www.geocities.com/sqlserverexamples
> > "pohkeat" <pohkeat@.hotmail.com> wrote in message
> > news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> > > Hi;
> > >
> > > I came across this article in SQL Server Magazine (March2003),under
> > Letters
> > > section, pg7, but I don't understand how to go abt implement or use
the
> > tip:
> > >
> > > " schedule a wrapper stored procedure that executs a procedure in
master
> > for
> > > each application database, such as;
> > >
> > > EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
> > >
> > > This SP will apparently get info on DB growth, data-file, table stats,
> > which
> > > is what I want to get.
> > >
> > > er....can someone show me the way what I need to do ?
> > >
> > >
> > > tks much
> > >
> > >
> >
> >
>