dbcc inputbuffer equivalent in SQL 2005

I know dbcc inputbuffer still exists in 2005, but what are the other ways to
check the underlying SQL running ?
btw, is dbcc inputbuffer a backward compatibility feature or is it still the
preferred command on 2005 ?
btw, what about sp_who2 ? Does the underlying command use the DMVs ?
fn_get_sql can be used
Take a look into the usage:
http://msdn2.microsoft.com/en-us/library/ms189451.aspx
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OJoEmv0FHHA.4580@.TK2MSFTNGP05.phx.gbl...
>I know dbcc inputbuffer still exists in 2005, but what are the other ways
>to check the underlying SQL running ?
> btw, is dbcc inputbuffer a backward compatibility feature or is it still
> the preferred command on 2005 ?
> btw, what about sp_who2 ? Does the underlying command use the DMVs ?
>
|||Hi Hassan
The new metadata objects in SQL 2005 are the sys.dm_exec_query_stats view,
and the sys.dm_exec_sql_text function. The follow query gets all the
sql_handles from the view, and uses them with the function to return the
text of the statement:
SELECT * FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
If you want to see how a stored procedure is written, you can look at the
definition for yourself:
SELECT object_definition(object_id('sp_who'))
You can also look at the definition of any DMV, catalog view or
compatability view using the object_definition function.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OJoEmv0FHHA.4580@.TK2MSFTNGP05.phx.gbl...
>I know dbcc inputbuffer still exists in 2005, but what are the other ways
>to check the underlying SQL running ?
> btw, is dbcc inputbuffer a backward compatibility feature or is it still
> the preferred command on 2005 ?
> btw, what about sp_who2 ? Does the underlying command use the DMVs ?
>

dbcc inputbuffer equivalent in SQL 2005

I know dbcc inputbuffer still exists in 2005, but what are the other ways to
check the underlying SQL running ?
btw, is dbcc inputbuffer a backward compatibility feature or is it still the
preferred command on 2005 ?
btw, what about sp_who2 ? Does the underlying command use the DMVs ?fn_get_sql can be used
Take a look into the usage:
http://msdn2.microsoft.com/en-us/library/ms189451.aspx
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OJoEmv0FHHA.4580@.TK2MSFTNGP05.phx.gbl...
>I know dbcc inputbuffer still exists in 2005, but what are the other ways
>to check the underlying SQL running ?
> btw, is dbcc inputbuffer a backward compatibility feature or is it still
> the preferred command on 2005 ?
> btw, what about sp_who2 ? Does the underlying command use the DMVs ?
>|||Hi Hassan
The new metadata objects in SQL 2005 are the sys.dm_exec_query_stats view,
and the sys.dm_exec_sql_text function. The follow query gets all the
sql_handles from the view, and uses them with the function to return the
text of the statement:
SELECT * FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
If you want to see how a stored procedure is written, you can look at the
definition for yourself:
SELECT object_definition(object_id('sp_who'))
You can also look at the definition of any DMV, catalog view or
compatability view using the object_definition function.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OJoEmv0FHHA.4580@.TK2MSFTNGP05.phx.gbl...
>I know dbcc inputbuffer still exists in 2005, but what are the other ways
>to check the underlying SQL running ?
> btw, is dbcc inputbuffer a backward compatibility feature or is it still
> the preferred command on 2005 ?
> btw, what about sp_who2 ? Does the underlying command use the DMVs ?
>

dbcc inputbuffer equivalent in SQL 2005

I know dbcc inputbuffer still exists in 2005, but what are the other ways to
check the underlying SQL running ?
btw, is dbcc inputbuffer a backward compatibility feature or is it still the
preferred command on 2005 ?
btw, what about sp_who2 ? Does the underlying command use the DMVs ?fn_get_sql can be used
Take a look into the usage:
http://msdn2.microsoft.com/en-us/library/ms189451.aspx
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OJoEmv0FHHA.4580@.TK2MSFTNGP05.phx.gbl...
>I know dbcc inputbuffer still exists in 2005, but what are the other ways
>to check the underlying SQL running ?
> btw, is dbcc inputbuffer a backward compatibility feature or is it still
> the preferred command on 2005 ?
> btw, what about sp_who2 ? Does the underlying command use the DMVs ?
>|||Hi Hassan
The new metadata objects in SQL 2005 are the sys.dm_exec_query_stats view,
and the sys.dm_exec_sql_text function. The follow query gets all the
sql_handles from the view, and uses them with the function to return the
text of the statement:
SELECT * FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
If you want to see how a stored procedure is written, you can look at the
definition for yourself:
SELECT object_definition(object_id('sp_who'))
You can also look at the definition of any DMV, catalog view or
compatability view using the object_definition function.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OJoEmv0FHHA.4580@.TK2MSFTNGP05.phx.gbl...
>I know dbcc inputbuffer still exists in 2005, but what are the other ways
>to check the underlying SQL running ?
> btw, is dbcc inputbuffer a backward compatibility feature or is it still
> the preferred command on 2005 ?
> btw, what about sp_who2 ? Does the underlying command use the DMVs ?
>

DBCC DBREINDEX

Hi,
Is a DBCC DBREINDEX for a clustered index for a table the equivalent to a
CREATE INDEX ...WITH DROP_EXISTING for a clustered index for a table? In
other words does the DBCC DBREINDEX statement in this scenario bypass the
automatic recreation of any nonclustered indexes when the clustered index is
rebuilt? Also, if the clustered index is ommited in the DBCC DBREINDEX
statement will the statement begin with the clustered index then proceed to
the nonclustered indexes so that the nonclustered indexes only have to be
rebuilt once by SQL Server?
Thanks
JerryJerry,
The main determinination of whether the nonclustered indexes are rebuilt
with DBREINDEX when a clustered index is rebuilt is if the CI is unique or
not. If the CI is unique then it does not have to rebuild the NCI's. If it
is not unique it will automatically rebuild all the NCI's. This changes in
2005 by the way. It never has to rebuild the NCI's regardless of uniqueness
or not. If you issue DBREINDEX without specifying any index it will rebuild
the CI first and then all the NCI's. If the CI was not unique it will not
rebuild the NCI's twice.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u7BdmxAqFHA.1032@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is a DBCC DBREINDEX for a clustered index for a table the equivalent to a
> CREATE INDEX ...WITH DROP_EXISTING for a clustered index for a table? In
> other words does the DBCC DBREINDEX statement in this scenario bypass the
> automatic recreation of any nonclustered indexes when the clustered index
> is rebuilt? Also, if the clustered index is ommited in the DBCC DBREINDEX
> statement will the statement begin with the clustered index then proceed
> to the nonclustered indexes so that the nonclustered indexes only have to
> be rebuilt once by SQL Server?
> Thanks
> Jerry
>|||That's pretty much the case.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u7BdmxAqFHA.1032@.TK2MSFTNGP09.phx.gbl...
Hi,
Is a DBCC DBREINDEX for a clustered index for a table the equivalent to a
CREATE INDEX ...WITH DROP_EXISTING for a clustered index for a table? In
other words does the DBCC DBREINDEX statement in this scenario bypass the
automatic recreation of any nonclustered indexes when the clustered index is
rebuilt? Also, if the clustered index is ommited in the DBCC DBREINDEX
statement will the statement begin with the clustered index then proceed to
the nonclustered indexes so that the nonclustered indexes only have to be
rebuilt once by SQL Server?
Thanks
Jerry|||Thanks Andrew. One of the better explanations I've ever read.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OdhNP3DqFHA.2444@.TK2MSFTNGP11.phx.gbl...
> Jerry,
> The main determinination of whether the nonclustered indexes are rebuilt
> with DBREINDEX when a clustered index is rebuilt is if the CI is unique or
> not. If the CI is unique then it does not have to rebuild the NCI's. If
> it is not unique it will automatically rebuild all the NCI's. This changes
> in 2005 by the way. It never has to rebuild the NCI's regardless of
> uniqueness or not. If you issue DBREINDEX without specifying any index it
> will rebuild the CI first and then all the NCI's. If the CI was not
> unique it will not rebuild the NCI's twice.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:u7BdmxAqFHA.1032@.TK2MSFTNGP09.phx.gbl...
>