Determining Security: March 2012

Thursday, March 29, 2012

Determine when currently executed job started

Hello!
I am trying to determine whether currently executed job is taking longer
than usual. I was wondering whether there is a way to determine start time
of currently executed job. I can use sp_help_job or xp_sqlagent_enum_jobs
but none of them reports this information.
Any advice is greatly appreciated,
Igor
Hi,
Query the MSDB..sysjobschedules table for below fields:-
active_start_date
active_end_date
active_start_time
active_end_time
Thanks
Hari
SQLServer MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:e8XavO6lFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I am trying to determine whether currently executed job is taking longer
> than usual. I was wondering whether there is a way to determine start time
> of currently executed job. I can use sp_help_job or xp_sqlagent_enum_jobs
> but none of them reports this information.
> Any advice is greatly appreciated,
> Igor
>
>
|||Hari,
Sorry, I didn't make myself clear. I would like to determine when job
that is running at this moment started. Unfortunately, fields you mentioned
do not provide this information. For example, I just scheduled a job to run
every 2 minutes continuously and I see that active_start_date=20050802 and
active_start_time=0.
I can collect data about currently running jobs using
xp_sqlagent_enum_jobs or sp_help_job but data I need is not reported by
those procedures.
Thanks,
Igor
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e6dVhS6lFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Query the MSDB..sysjobschedules table for below fields:-
> active_start_date
> active_end_date
> active_start_time
> active_end_time
> Thanks
> Hari
> SQLServer MVP
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:e8XavO6lFHA.1148@.TK2MSFTNGP12.phx.gbl...
>

Determine when currently executed job started

Hello!
I am trying to determine whether currently executed job is taking longer
than usual. I was wondering whether there is a way to determine start time
of currently executed job. I can use sp_help_job or xp_sqlagent_enum_jobs
but none of them reports this information.
Any advice is greatly appreciated,
IgorHi,
Query the MSDB..sysjobschedules table for below fields:-
active_start_date
active_end_date
active_start_time
active_end_time
Thanks
Hari
SQLServer MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:e8XavO6lFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I am trying to determine whether currently executed job is taking longer
> than usual. I was wondering whether there is a way to determine start time
> of currently executed job. I can use sp_help_job or xp_sqlagent_enum_jobs
> but none of them reports this information.
> Any advice is greatly appreciated,
> Igor
>
>|||Hari,
Sorry, I didn't make myself clear. I would like to determine when job
that is running at this moment started. Unfortunately, fields you mentioned
do not provide this information. For example, I just scheduled a job to run
every 2 minutes continuously and I see that active_start_date=20050802 and
active_start_time=0.
I can collect data about currently running jobs using
xp_sqlagent_enum_jobs or sp_help_job but data I need is not reported by
those procedures.
Thanks,
Igor
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e6dVhS6lFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Query the MSDB..sysjobschedules table for below fields:-
> active_start_date
> active_end_date
> active_start_time
> active_end_time
> Thanks
> Hari
> SQLServer MVP
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:e8XavO6lFHA.1148@.TK2MSFTNGP12.phx.gbl...
>

Determine when currently executed job started

Hello!
I am trying to determine whether currently executed job is taking longer
than usual. I was wondering whether there is a way to determine start time
of currently executed job. I can use sp_help_job or xp_sqlagent_enum_jobs
but none of them reports this information.
Any advice is greatly appreciated,
IgorHi,
Query the MSDB..sysjobschedules table for below fields:-
active_start_date
active_end_date
active_start_time
active_end_time
Thanks
Hari
SQLServer MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:e8XavO6lFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I am trying to determine whether currently executed job is taking longer
> than usual. I was wondering whether there is a way to determine start time
> of currently executed job. I can use sp_help_job or xp_sqlagent_enum_jobs
> but none of them reports this information.
> Any advice is greatly appreciated,
> Igor
>
>|||Hari,
Sorry, I didn't make myself clear. I would like to determine when job
that is running at this moment started. Unfortunately, fields you mentioned
do not provide this information. For example, I just scheduled a job to run
every 2 minutes continuously and I see that active_start_date=20050802 and
active_start_time=0.
I can collect data about currently running jobs using
xp_sqlagent_enum_jobs or sp_help_job but data I need is not reported by
those procedures.
Thanks,
Igor
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e6dVhS6lFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Query the MSDB..sysjobschedules table for below fields:-
> active_start_date
> active_end_date
> active_start_time
> active_end_time
> Thanks
> Hari
> SQLServer MVP
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:e8XavO6lFHA.1148@.TK2MSFTNGP12.phx.gbl...
>> Hello!
>> I am trying to determine whether currently executed job is taking longer
>> than usual. I was wondering whether there is a way to determine start
>> time of currently executed job. I can use sp_help_job or
>> xp_sqlagent_enum_jobs but none of them reports this information.
>> Any advice is greatly appreciated,
>> Igor
>>
>sql

Determine when a Service Pack was applied?

We are running SQL Server 2000 with SP3. Is there a way to determine
the date the last service pack was applied?
Thanks
Randy K wrote:
> We are running SQL Server 2000 with SP3. Is there a way to determine
> the date the last service pack was applied?
> Thanks
You could look in the error log and see when that version was first
booted (assuming they have no been overwritten). Use SQL EM or use the
undocumented procedures from Query Analyzer:
Exec sp_enumerrorlogs -- returns each error log and it's Archive #
Exec sp_readerrorlog -- no parameter for current log or pass in archive
# to read another log
David Gugick - SQL Server MVP
Quest Software
|||Look for the Sqlsp.log in your Windows directory. It's the logfile from
your last Service pack installation.
Markus

Determine when a Service Pack was applied?

We are running SQL Server 2000 with SP3. Is there a way to determine
the date the last service pack was applied?
ThanksRandy K wrote:
> We are running SQL Server 2000 with SP3. Is there a way to determine
> the date the last service pack was applied?
> Thanks
You could look in the error log and see when that version was first
booted (assuming they have no been overwritten). Use SQL EM or use the
undocumented procedures from Query Analyzer:
Exec sp_enumerrorlogs -- returns each error log and it's Archive #
Exec sp_readerrorlog -- no parameter for current log or pass in archive
# to read another log
David Gugick - SQL Server MVP
Quest Software|||Look for the Sqlsp.log in your Windows directory. It's the logfile from
your last Service pack installation.
Markus

Determine when a Service Pack was applied?

We are running SQL Server 2000 with SP3. Is there a way to determine
the date the last service pack was applied?
ThanksRandy K wrote:
> We are running SQL Server 2000 with SP3. Is there a way to determine
> the date the last service pack was applied?
> Thanks
You could look in the error log and see when that version was first
booted (assuming they have no been overwritten). Use SQL EM or use the
undocumented procedures from Query Analyzer:
Exec sp_enumerrorlogs -- returns each error log and it's Archive #
Exec sp_readerrorlog -- no parameter for current log or pass in archive
# to read another log
David Gugick - SQL Server MVP
Quest Software|||Look for the Sqlsp.log in your Windows directory. It's the logfile from
your last Service pack installation.
Markus

Determine what version of RS is insalled

How do you determine what version of Reporting Services is installed int the DB and Web server ( I have the DB on a different server than the Web server)

Thanks

Try typing
http://localhost/Reportserver

Determine what ver/SP is installed for Reporting Services

How can you tell what version of Reporting Services is installed?Just run the url http://localhost/ReportServer or
http://<hostname>/ReportServer|||That is where to look, however I didn't know what the numbers meant. I
found this in another thread:
base install = 743
sp1 = 878
sp2 = 1042|||If you run the url. at the bottom it will display the version information
like following
Microsoft SQL Server Reporting Services Version 8.00.1038.00sql

Determine what User Account SQL Server is running under

(MS SQL 2000)
Is there a way to determine what network user account SQL Server is
installed and running under? More importantly, can I easily change it?
Thanks!If you have permissions to the server have a look in Control Panel /
Services / MSSQLServer / Logon Tab
It can be easily changed but you do need to stop/start the services for it
to take effect
--
HTH. Ryan
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:uLoxF9TEGHA.1508@.TK2MSFTNGP15.phx.gbl...
> (MS SQL 2000)
> Is there a way to determine what network user account SQL Server is
> installed and running under? More importantly, can I easily change it?
> Thanks!
>|||Thanks! That was pretty easy.
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23zgi8AUEGHA.2724@.TK2MSFTNGP12.phx.gbl...
> If you have permissions to the server have a look in Control Panel /
> Services / MSSQLServer / Logon Tab
> It can be easily changed but you do need to stop/start the services for it
> to take effect
> --
> HTH. Ryan
>
> "D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:uLoxF9TEGHA.1508@.TK2MSFTNGP15.phx.gbl...
>> (MS SQL 2000)
>> Is there a way to determine what network user account SQL Server is
>> installed and running under? More importantly, can I easily change it?
>> Thanks!
>>
>|||Hi,
The proper method to change the account is via the Enterprise Manager or you
have to grant the new account some additional permissions on the server. I do
not remember all of the permissions, but they include 'act as part of os',
'run as service', etc.
If the account is changed via the EM, all of the required permisions are
automatically granted.
hth
DeeJay Puar
MCDBA
"D. Shane Fowlkes" wrote:
> Thanks! That was pretty easy.
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%23zgi8AUEGHA.2724@.TK2MSFTNGP12.phx.gbl...
> > If you have permissions to the server have a look in Control Panel /
> > Services / MSSQLServer / Logon Tab
> >
> > It can be easily changed but you do need to stop/start the services for it
> > to take effect
> >
> > --
> > HTH. Ryan
> >
> >
> > "D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> > news:uLoxF9TEGHA.1508@.TK2MSFTNGP15.phx.gbl...
> >> (MS SQL 2000)
> >>
> >> Is there a way to determine what network user account SQL Server is
> >> installed and running under? More importantly, can I easily change it?
> >>
> >> Thanks!
> >>
> >>
> >
> >
>
>|||Thanks again.
"DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
news:5881C956-4A72-4868-A3BC-1E3907C3667E@.microsoft.com...
> Hi,
> The proper method to change the account is via the Enterprise Manager or
> you
> have to grant the new account some additional permissions on the server. I
> do
> not remember all of the permissions, but they include 'act as part of os',
> 'run as service', etc.
> If the account is changed via the EM, all of the required permisions are
> automatically granted.
> hth
> DeeJay Puar
> MCDBA
> "D. Shane Fowlkes" wrote:
>> Thanks! That was pretty easy.
>>
>> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
>> news:%23zgi8AUEGHA.2724@.TK2MSFTNGP12.phx.gbl...
>> > If you have permissions to the server have a look in Control Panel /
>> > Services / MSSQLServer / Logon Tab
>> >
>> > It can be easily changed but you do need to stop/start the services for
>> > it
>> > to take effect
>> >
>> > --
>> > HTH. Ryan
>> >
>> >
>> > "D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
>> > news:uLoxF9TEGHA.1508@.TK2MSFTNGP15.phx.gbl...
>> >> (MS SQL 2000)
>> >>
>> >> Is there a way to determine what network user account SQL Server is
>> >> installed and running under? More importantly, can I easily change
>> >> it?
>> >>
>> >> Thanks!
>> >>
>> >>
>> >
>> >
>>

Determine what SQL DB Report Server uses

Is there a way to determine what SQL server/database an installation of
report server is using?
The information is encrypted within the config file and the rsconfig
utility looks like it can only be used to set new values.
Thanks.
-- Brianby default (unless you changed the name during installation), it should be
ReportServer & ReportServerTempDB.
if you did change the default name, look for xxx & xxxTempDB in SQL Server.
rob
"Brian" wrote:
> Is there a way to determine what SQL server/database an installation of
> report server is using?
> The information is encrypted within the config file and the rsconfig
> utility looks like it can only be used to set new values.
> Thanks.
> -- Brian
>|||Thanks Rob.
I was hoping that the installation of report server would be able to
show me which SQL server it was using (SQL was not installed locally),
but it doesn't look as though there is a way to view any of that
configuration information.
I ended up monitoring a few SQL instances looking for connections from
the report server to determine which SQL box it was using. Too bad that
info isn't more accessible through some of the tools.

Determine what server i'm running on

I've generated a report that will need to display different information
based upon where it is being run. For instance, if the report is executing
on our DVLP server, i need a title to say "Developement". Is there is simple
way to determine what environment the report is being run in? Check the URL?
Server variables?
Thanks,
--
Brian Grant
Senior Programmer
SI International
www.si-intl.com=System.Environment.MachineName
Warning: You'll need to give expression host FullTrust in order to use this
expression on report server, which may be a security risk.
If your SQL Server is on the same machine, creating a dataset against it and
using SELECT @.@.servername would be a better approach.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:uZDVm2lbEHA.3988@.tk2msftngp13.phx.gbl...
> I've generated a report that will need to display different information
> based upon where it is being run. For instance, if the report is executing
> on our DVLP server, i need a title to say "Developement". Is there is
simple
> way to determine what environment the report is being run in? Check the
URL?
> Server variables?
> Thanks,
> --
> Brian Grant
> Senior Programmer
> SI International
> www.si-intl.com
>sql

Determine what procedure/function called a stored procedure

Is there a way within a proc to determine what object called it?
ex:
DB1.dbo.ProcA calls DB2.dbo.ProcB. From within ProcB, is there any means to
get the context (name or id) of the procedure or function that called it?
Thanks!I don't think so. I think all you get is @.@.NESTLEVEL
However you can set sysprocesses.CONTEXT_INFO
I've used it to bypass triggers before.
http://www.sqlservercentral.com/col...ingtriggers.asp
In this case it's much the same as updating an @.@.spid based table.
Paul
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:047E3AF3-D480-4246-899B-A0616B51F179@.microsoft.com...
> Is there a way within a proc to determine what object called it?
> ex:
> DB1.dbo.ProcA calls DB2.dbo.ProcB. From within ProcB, is there any means
> to
> get the context (name or id) of the procedure or function that called it?
> Thanks!|||nrgh...that is what I was afraid of. :)
I have to modify some existing procedures and the original implementation
has led to some issues in logging information. I was hoping to avoid
changing about 1000 procs to pass in the DBID that it was executed on. *sig
h*
Thanks for the confirmation.
"Paul Cahill" wrote:

> I don't think so. I think all you get is @.@.NESTLEVEL
> However you can set sysprocesses.CONTEXT_INFO
> I've used it to bypass triggers before.
> http://www.sqlservercentral.com/col...ingtriggers.asp
> In this case it's much the same as updating an @.@.spid based table.
> Paul
>
>
> "Travis" <Travis@.discussions.microsoft.com> wrote in message
> news:047E3AF3-D480-4246-899B-A0616B51F179@.microsoft.com...
>
>|||The @.@.procid variable returns the procedure identifier of the current
procedure. Perhaps you can add a parameter to each SP @.called_by and call
procedures like so:
exec @.proc2 @.called_by = @.@.procid
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:047E3AF3-D480-4246-899B-A0616B51F179@.microsoft.com...
> Is there a way within a proc to determine what object called it?
> ex:
> DB1.dbo.ProcA calls DB2.dbo.ProcB. From within ProcB, is there any means
> to
> get the context (name or id) of the procedure or function that called it?
> Thanks!|||There's been a few times I'd like to be able to get at the stack too.
Anyone out there know a trick?
If these were remote calls I think it could be done by setting up the linked
servers such that rpc's always connect as a particular user.
If each link is given a different username can you then tell which server
remotely called the proc.
But you are executing all on the same machine just from different databases.
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:DA1631A4-10C6-4037-97F1-C5D58327B16A@.microsoft.com...
> nrgh...that is what I was afraid of. :)
> I have to modify some existing procedures and the original implementation
> has led to some issues in logging information. I was hoping to avoid
> changing about 1000 procs to pass in the DBID that it was executed on.
> *sigh*
> Thanks for the confirmation.
> "Paul Cahill" wrote:
>|||The procID is being passed into the procedure, but it will need to pass in
the DBID as well. There are multiple DBs on the server and it recently came
to light that the logic in the proc to determine which DB the proc was
executed on is incorrect (as the procIds can be the same in multiple dbs).
: )
Thx
"JT" wrote:

> The @.@.procid variable returns the procedure identifier of the current
> procedure. Perhaps you can add a parameter to each SP @.called_by and call
> procedures like so:
> exec @.proc2 @.called_by = @.@.procid
> "Travis" <Travis@.discussions.microsoft.com> wrote in message
> news:047E3AF3-D480-4246-899B-A0616B51F179@.microsoft.com...
>
>|||If you are wanting to peform procedure call stack tracing for debugging
purposes, then look into using SQL Profiler for this. It would require no
programming changes.
http://msdn.microsoft.com/library/d...nEventClues.asp
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:4C79AB11-F7D1-40DD-BB52-0063E138D8C4@.microsoft.com...
> The procID is being passed into the procedure, but it will need to pass in
> the DBID as well. There are multiple DBs on the server and it recently
> came
> to light that the logic in the proc to determine which DB the proc was
> executed on is incorrect (as the procIds can be the same in multiple dbs).
> : )
> Thx
> "JT" wrote:
>

determine what is the current database in a stored proc

Hi,
I am writing a stored proc that has to change to a different database
inline. Example:
use database2
go
What I need to do first is find out what the name of the current
database is before I switch to database2 so I can switch back.
How do I determine what the current database is?
Thanks
Randy
Randy,
Try SELECT DB_NAME()
HTH
Jerry
"Randy" <no_freakin_spam@.sickofit.com> wrote in message
news:MPG.1d932eeaaa07777a989697@.news.supernews.com ...
> Hi,
> I am writing a stored proc that has to change to a different database
> inline. Example:
>
> use database2
> go
>
> What I need to do first is find out what the name of the current
> database is before I switch to database2 so I can switch back.
>
> How do I determine what the current database is?
>
> Thanks
> Randy
|||Thanks
Seems obvious now.
In article <OCvORgguFHA.3608@.TK2MSFTNGP10.phx.gbl>, jspivey@.vestas-
awt.com says...
> Randy,
> Try SELECT DB_NAME()
> HTH
> Jerry
> "Randy" <no_freakin_spam@.sickofit.com> wrote in message
> news:MPG.1d932eeaaa07777a989697@.news.supernews.com ...
>
>

determine what is the current database in a stored proc

Hi,
I am writing a stored proc that has to change to a different database
inline. Example:
use database2
go
What I need to do first is find out what the name of the current
database is before I switch to database2 so I can switch back.
How do I determine what the current database is?
Thanks
RandyRandy,
Try SELECT DB_NAME()
HTH
Jerry
"Randy" <no_freakin_spam@.sickofit.com> wrote in message
news:MPG.1d932eeaaa07777a989697@.news.supernews.com...
> Hi,
> I am writing a stored proc that has to change to a different database
> inline. Example:
>
> use database2
> go
>
> What I need to do first is find out what the name of the current
> database is before I switch to database2 so I can switch back.
>
> How do I determine what the current database is?
>
> Thanks
> Randy|||Thanks
Seems obvious now.
In article <OCvORgguFHA.3608@.TK2MSFTNGP10.phx.gbl>, jspivey@.vestas-
awt.com says...
> Randy,
> Try SELECT DB_NAME()
> HTH
> Jerry
> "Randy" <no_freakin_spam@.sickofit.com> wrote in message
> news:MPG.1d932eeaaa07777a989697@.news.supernews.com...
> > Hi,
> >
> > I am writing a stored proc that has to change to a different database
> > inline. Example:
> >
> >
> > use database2
> > go
> >
> >
> > What I need to do first is find out what the name of the current
> > database is before I switch to database2 so I can switch back.
> >
> >
> > How do I determine what the current database is?
> >
> >
> > Thanks
> >
> > Randy
>
>

determine what is the current database in a stored proc

Hi,
I am writing a stored proc that has to change to a different database
inline. Example:
use database2
go
What I need to do first is find out what the name of the current
database is before I switch to database2 so I can switch back.
How do I determine what the current database is?
Thanks
RandyRandy,
Try SELECT DB_NAME()
HTH
Jerry
"Randy" <no_freakin_spam@.sickofit.com> wrote in message
news:MPG.1d932eeaaa07777a989697@.news.supernews.com...
> Hi,
> I am writing a stored proc that has to change to a different database
> inline. Example:
>
> use database2
> go
>
> What I need to do first is find out what the name of the current
> database is before I switch to database2 so I can switch back.
>
> How do I determine what the current database is?
>
> Thanks
> Randy|||Thanks
Seems obvious now.
In article <OCvORgguFHA.3608@.TK2MSFTNGP10.phx.gbl>, jspivey@.vestas-
awt.com says...
> Randy,
> Try SELECT DB_NAME()
> HTH
> Jerry
> "Randy" <no_freakin_spam@.sickofit.com> wrote in message
> news:MPG.1d932eeaaa07777a989697@.news.supernews.com...
>
>

Determine used fields/tables in database

Greetings to all!
I was curious if there was a way to determine what fields are in use by
stored procedures and/or views in a database. If there's a way through
SQL or through VB .NET, I would be most appreciative to learn about it.
Thanks,
Kyjan
"Kyjan" <HolySaphAngel@.gmail.com> wrote in message
news:1141065761.449583.226070@.u72g2000cwu.googlegr oups.com...
> Greetings to all!
> I was curious if there was a way to determine what fields are in use by
> stored procedures and/or views in a database. If there's a way through
> SQL or through VB .NET, I would be most appreciative to learn about it.
> Thanks,
> Kyjan
>
There may be an easier way, but you might look at how the sp_depends sproc
does it.
Rick Sawtell
MCT, MCSD, MCDBA
|||This way does not look at views. Is there a way to include those?
Kyjan
sql

Determine used fields/tables in database

Greetings to all!
I was curious if there was a way to determine what fields are in use by
stored procedures and/or views in a database. If there's a way through
SQL or through VB .NET, I would be most appreciative to learn about it.
Thanks,
Kyjan"Kyjan" <HolySaphAngel@.gmail.com> wrote in message
news:1141065761.449583.226070@.u72g2000cwu.googlegroups.com...
> Greetings to all!
> I was curious if there was a way to determine what fields are in use by
> stored procedures and/or views in a database. If there's a way through
> SQL or through VB .NET, I would be most appreciative to learn about it.
> Thanks,
> Kyjan
>
There may be an easier way, but you might look at how the sp_depends sproc
does it.
Rick Sawtell
MCT, MCSD, MCDBA|||This way does not look at views. Is there a way to include those?
Kyjan

Determine used fields/tables in database

Greetings to all!
I was curious if there was a way to determine what fields are in use by
stored procedures and/or views in a database. If there's a way through
SQL or through VB .NET, I would be most appreciative to learn about it.
Thanks,
Kyjan"Kyjan" <HolySaphAngel@.gmail.com> wrote in message
news:1141065761.449583.226070@.u72g2000cwu.googlegroups.com...
> Greetings to all!
> I was curious if there was a way to determine what fields are in use by
> stored procedures and/or views in a database. If there's a way through
> SQL or through VB .NET, I would be most appreciative to learn about it.
> Thanks,
> Kyjan
>
There may be an easier way, but you might look at how the sp_depends sproc
does it.
Rick Sawtell
MCT, MCSD, MCDBA|||This way does not look at views. Is there a way to include those?
Kyjan

Determine updated and inserted rows

I'm working on an algorithm to backup data from pocket pc sql server 2.0
database involved in an merge replication with sql server 2000 sp3 with rda
in case that replication fails from whatever reason. My question is if I can
determine on a sql server ce 2.0 table involved in merge replication what
rows have been updated or inserted form last replication in order to
transfer only those rows on backup database. I've noticed that inserted rows
have s_Generation field 0 but I have no clue about updated rows.
Any changed (inserted or updated) row in ce database which is part of a
replicated table will have the s_generation column set to zero until the
next sync. So this is just an indicator of changed rows between two
successive syncs.
thanks
gopal

Determine top 25 rows in a table that includes a ntext data type.

We have a table that has ~900,000 rows in the table. The table previously had
~1.5 million rows of data when the ~600,000 rows where deleted the table size
shrunk ~1 G. The table is currently useing ~27 GB of storage.
I would like to write a query to determine my largest 25 rows. This is
being done to determine if the storage is truely being used.
C,
Perhaps:
SELECT TOP 25 RowKey, LEN(NTextColumn)
FROM YourTable
ORDER BY LEN(NTextColumn) DESC
RLF
"C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
> We have a table that has ~900,000 rows in the table. The table previously
> had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table
> size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
|||I'm attemping to run it now. I had to use DATALENGTH instead of LEN for the
NTEXT field.
I was hoping to find a was to gather the information using system tables ie
number of pages used by row.
"Russell Fields" wrote:

> C,
> Perhaps:
> SELECT TOP 25 RowKey, LEN(NTextColumn)
> FROM YourTable
> ORDER BY LEN(NTextColumn) DESC
> RLF
> "C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
> news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
>
>
|||> I was hoping to find a was to gather the information using system tables
> ie
> number of pages used by row.
No system table stores this information... there might be some undocumented
DBCC command that does it, but even that would probably be coming from the
page angle, not the row angle.
|||That's what I was affraid of. I used DBCC IND to get the page information.
The other technique was taking to long to run so I stopped it.
"Aaron Bertrand [SQL Server MVP]" wrote:

> No system table stores this information... there might be some undocumented
> DBCC command that does it, but even that would probably be coming from the
> page angle, not the row angle.
>
>
|||> That's what I was affraid of. I used DBCC IND to get the page
> information.
Did you figure out the top 25 rows already? If not...

> The other technique was taking to long to run so I stopped it.
...maybe you could try doing this in two steps, e.g.
SELECT pk, tcl = DATALENGTH(text_col) INTO #foo FROM main_table;
SELECT TOP 25 pk, tcl FROM #foo ORDER BY tcl DESC;
This might be a lot cheaper then including the datalength() calculation and
the ordering in the same step.
|||C RAMSEY wrote:
> We have a table that has ~900,000 rows in the table. The table previously had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
When you delete rows, the space that was occupied might not be released.
It might be retained as "unused space", and reused for future rows.
If it is your goal to lower the used storage, then you could first run
sp_spaceused to determine the amount of unused space and/or DBCC
SHOWCONTIG to determine the average page density. If there is a lot of
unused space, then make sure you have a clustered index (or add one) and
run DBCC INDEXDEFRAG or DBCC DBREINDEX / ALTER TABLE to remove the
unused space.
HTH,
Gert-Jan

Determine top 25 rows in a table that includes a ntext data type.

We have a table that has ~900,000 rows in the table. The table previously had
~1.5 million rows of data when the ~600,000 rows where deleted the table size
shrunk ~1 G. The table is currently useing ~27 GB of storage.
I would like to write a query to determine my largest 25 rows. This is
being done to determine if the storage is truely being used.C,
Perhaps:
SELECT TOP 25 RowKey, LEN(NTextColumn)
FROM YourTable
ORDER BY LEN(NTextColumn) DESC
RLF
"C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
> We have a table that has ~900,000 rows in the table. The table previously
> had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table
> size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.|||I'm attemping to run it now. I had to use DATALENGTH instead of LEN for the
NTEXT field.
I was hoping to find a was to gather the information using system tables ie
number of pages used by row.
"Russell Fields" wrote:
> C,
> Perhaps:
> SELECT TOP 25 RowKey, LEN(NTextColumn)
> FROM YourTable
> ORDER BY LEN(NTextColumn) DESC
> RLF
> "C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
> news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
> > We have a table that has ~900,000 rows in the table. The table previously
> > had
> > ~1.5 million rows of data when the ~600,000 rows where deleted the table
> > size
> > shrunk ~1 G. The table is currently useing ~27 GB of storage.
> >
> > I would like to write a query to determine my largest 25 rows. This is
> > being done to determine if the storage is truely being used.
>
>|||> I was hoping to find a was to gather the information using system tables
> ie
> number of pages used by row.
No system table stores this information... there might be some undocumented
DBCC command that does it, but even that would probably be coming from the
page angle, not the row angle.|||That's what I was affraid of. I used DBCC IND to get the page information.
The other technique was taking to long to run so I stopped it.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > I was hoping to find a was to gather the information using system tables
> > ie
> > number of pages used by row.
> No system table stores this information... there might be some undocumented
> DBCC command that does it, but even that would probably be coming from the
> page angle, not the row angle.
>
>|||> That's what I was affraid of. I used DBCC IND to get the page
> information.
Did you figure out the top 25 rows already? If not...
> The other technique was taking to long to run so I stopped it.
...maybe you could try doing this in two steps, e.g.
SELECT pk, tcl = DATALENGTH(text_col) INTO #foo FROM main_table;
SELECT TOP 25 pk, tcl FROM #foo ORDER BY tcl DESC;
This might be a lot cheaper then including the datalength() calculation and
the ordering in the same step.|||C RAMSEY wrote:
> We have a table that has ~900,000 rows in the table. The table previously had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
When you delete rows, the space that was occupied might not be released.
It might be retained as "unused space", and reused for future rows.
If it is your goal to lower the used storage, then you could first run
sp_spaceused to determine the amount of unused space and/or DBCC
SHOWCONTIG to determine the average page density. If there is a lot of
unused space, then make sure you have a clustered index (or add one) and
run DBCC INDEXDEFRAG or DBCC DBREINDEX / ALTER TABLE to remove the
unused space.
HTH,
Gert-Jansql

determine time lapse between 2 rows

I have a table of machine data that captures fault codes, time machine stopped and time machine started. I can easily calculate the downtime, but how do i take the last start time and subtract it from the next stop time (1 row from the next row) to get an up time. I need it to group by short date and fault as I intend to use Excel as a reporting tool and pulling in all data will not fit. What is the most efficient way?
Thanks,
Leei guess you could do it..... but it will be easier for us to explain if you could post some default table values and table structures...|||well u will have to make a self-join for this to happen. something like below

select top 1 from [same table] where [time machine stopped] > outer.[time machine started] and MachineCode=outer.MachineCode etc.. etc...|||Sorry for the delay. I am traveling. Here is some test data|||I tried the self join and did get a return for 1 row. However, I think that I did a poor job of stating my goal. I will try and clairfy what I am trying to accomplish. For a given date range I need to first return the difference in the DWNTIMESTAMP_VAL0 and UPTIMESTAMP_VALO for that given row (this gives me the outage time). I also need to take the DWNTIMESTAMP_VAL0 for the very next row and find the difference from the UPTIMESTAMP_VAL0 the previous row (this gives me the uptime between the two downtime events). I will need this for each row in the date range. If the math part makes this more difficult, I can get the math done a different way if I could return a RS with row 1's DWNTIMESTAMP_VAL0, UPTIMESTAMP_VAL0 and row 2's DWNTIMESTAMP_VAL0 for a new row 1, row 2's DWNTIMESTAMP_VAL0, UPTIMESTAMP_VAL0 and row 3's DWNTIMESTAMP_VAL0 for a new row 2, ... Is this at all possible to do within SQL?|||Hope following procedure will help to solve your problem:

Create Procedure sp_getNextUpTiming
As
Begin

Select * Into #TempDOWNTIMELOG From [DOWNTIMELOG]

Alter Table #TempDOWNTIMELOG Add RowId INT Identity(1, 1)

Select
DATEDIFF(SS, CONVERT(DATETIME, D1D, 101) , CONVERT(DATETIME, D1U, 101)) As DownTime,
DATEDIFF(SS, CONVERT(DATETIME, D1U, 101) , CONVERT(DATETIME, D2D, 101)) As NextUpTime
From
(
Select
D1.[DWNTIMESTAMP_VAL0] As D1D, D1.[UPTIMESTAMP_VAL0] As D1U,
D2.[DWNTIMESTAMP_VAL0] As D2D, D2.[UPTIMESTAMP_VAL0] As D2U
From #TempDOWNTIMELOG As D1
Inner Join #TempDOWNTIMELOG As D2 On D1.RowId In
(Select Max(RowId) From #TempDOWNTIMELOG Where RowId<D2.RowId)
) As tblTimings

End

Prajkta A.
------
Software Engineer
Clarion Technologies
(SEI CMMI Level 3)
Pune, India
www. clariontechnologies. co. in|||When I call the procedure, it fails with Invaild Column Name "RowId"|||I am so sorry for your inconvenience. Please try this:

Alter Procedure sp_getNextUpTiming
As
Begin

Select * Into #TempDOWNTIMELOG From [DOWNTIMELOG]

Alter Table #TempDOWNTIMELOG Add RowId INT Identity(1, 1)

DECLARE @.strTest vARCHAR(4000)
set @.strTest = 'Select
DATEDIFF(SS, CONVERT(DATETIME, D1D, 101) , CONVERT(DATETIME, D1U, 101)) As DownTime,
DATEDIFF(SS, CONVERT(DATETIME, D1U, 101) , CONVERT(DATETIME, D2D, 101)) As NextUpTime
From
(
Select
D1.[DWNTIMESTAMP_VAL0] As D1D, D1.[UPTIMESTAMP_VAL0] As D1U,
D2.[DWNTIMESTAMP_VAL0] As D2D, D2.[UPTIMESTAMP_VAL0] As D2U
From #TempDOWNTIMELOG As D1
Inner Join #TempDOWNTIMELOG As D2 On D1.RowId In
(Select Max(RowId) From #TempDOWNTIMELOG Where RowId<D2.RowId)
) As tblTimings'

EXEC(@.strTest)

End|||thanks so much!! able to return the uptime and downtime. i will now attempt to gain enough understanding so that i can do group by as there are different pieces of equipment and i need to make certain that i pull the data by line.

Determine the type of trigger

I would like to know how to determine what type of trigger is occurring -
I am getting syntax errors with the following code
CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
If delete then
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER),'Delete', INSERTED.* FROM Inserted
End If
If insert then
INSERT INTO AuditAbatements SELECT GETDATE(),convert(char(30),
CURRENT_USER),'Insert', INSERTED.* FROM Inserted
End If
If update then
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER),'Update', INSERTED.* FROM Inserted
End If
ENDThe inserteD and deleteD (note the D on the end) virtual tables used inside
of triggered represent changes made to the table the trigger is 'ON' by a
single transaction. For a transaction that is an INSERT, the inserted virtua
l
table will contain the new rows to be added (the deleted virtual table will
be empty). For a transaction that is a DELETE, the deleted virtual table wil
l
have the rows to be removed (the inserted virtual table will be empty). For
a
transaction that is an UPDATE, the deleted virtual table will have the old
(current) values, and the inserted virtual table will have the new values
specified by the update.
Your code fails because you can't use the keywords
'insert','delete','update' in a boolean expression like you did.
You need something like this
CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @.insertedcount int
DECLARE @.deletedcount int
SELECT @.insertedcount = COUNT(*) FROM inserted
SELECT @.deletedcount = COUNT(*) FROM deleted
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER),
CASE
WHEN @.insertedcount = 0 THEN 'DELETE'
WHEN @.deletedcount = 0 THEN 'INSERT'
ELSE 'UPDATE'
END,
INSERTED.* FROM Inserted
END--
"CSHARPITPRO" wrote:

> I would like to know how to determine what type of trigger is occurring -
> I am getting syntax errors with the following code
> CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
> FOR INSERT, UPDATE, DELETE
> AS
>
> BEGIN
> If delete then
> INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
> CURRENT_USER),'Delete', INSERTED.* FROM Inserted
> End If
> If insert then
> INSERT INTO AuditAbatements SELECT GETDATE(),convert(char(30),
> CURRENT_USER),'Insert', INSERTED.* FROM Inserted
> End If
> If update then
> INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
> CURRENT_USER),'Update', INSERTED.* FROM Inserted
> End If
> END
>|||if this is the actual code, then the problem is in the "if delete |
insert | update then" lines. There is no such statement.
[There is an UPDATE() function that is available inside a trigger to
determine if a particular column has updated rows, but that would be
different than the attempted statement(s)]
Also, in addition to the inserted virtual table, there is a deleted
virtual table that is present for updates and deletes.
Look up CREATE TRIGGER in BOL for more details on using these.
there's no error if you try to access these virtual table and they
aren't there, so you can have a single insert
you will need to explicitly name your columns, instead of using inserted.*
also, this example assumes, as in the op, that only the modified rows'
values are being put into the audit table.
e.g.
create trigger [trgTest] on [dbo].[Abatements]
for insert, update, delete
as
begin
insert into AuditAbatements (<col names here> )
select getdate(), convert(char(30), current_user),
case when inserted.pk_col is null then 'Delete'
when deleted.pk_col is null then 'Insert'
else 'Update' end,
isnull(inserted.column1, deleted.column1),...etc...,
isnull(inserted.columnN, deleted.columnN)
from inserted
full join deleted on inserted.pk_col = deleted.pk_col
end
CSHARPITPRO wrote:
> I would like to know how to determine what type of trigger is occurring -
> I am getting syntax errors with the following code
> CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
> FOR INSERT, UPDATE, DELETE
> AS
>
> BEGIN
> If delete then
> INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
> CURRENT_USER),'Delete', INSERTED.* FROM Inserted
> End If
> If insert then
> INSERT INTO AuditAbatements SELECT GETDATE(),convert(char(30),
> CURRENT_USER),'Insert', INSERTED.* FROM Inserted
> End If
> If update then
> INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
> CURRENT_USER),'Update', INSERTED.* FROM Inserted
> End If
> END
>|||Thanks Mark,
You have really helped me today!
"Mark Williams" wrote:
> The inserteD and deleteD (note the D on the end) virtual tables used insid
e
> of triggered represent changes made to the table the trigger is 'ON' by a
> single transaction. For a transaction that is an INSERT, the inserted virt
ual
> table will contain the new rows to be added (the deleted virtual table wil
l
> be empty). For a transaction that is a DELETE, the deleted virtual table w
ill
> have the rows to be removed (the inserted virtual table will be empty). Fo
r a
> transaction that is an UPDATE, the deleted virtual table will have the old
> (current) values, and the inserted virtual table will have the new values
> specified by the update.
> Your code fails because you can't use the keywords
> 'insert','delete','update' in a boolean expression like you did.
> You need something like this
> CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
> FOR INSERT, UPDATE, DELETE
> AS
> BEGIN
> DECLARE @.insertedcount int
> DECLARE @.deletedcount int
> SELECT @.insertedcount = COUNT(*) FROM inserted
> SELECT @.deletedcount = COUNT(*) FROM deleted
> INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
> CURRENT_USER),
> CASE
> WHEN @.insertedcount = 0 THEN 'DELETE'
> WHEN @.deletedcount = 0 THEN 'INSERT'
> ELSE 'UPDATE'
> END,
> INSERTED.* FROM Inserted
> END--
>
> "CSHARPITPRO" wrote:
>|||Well, I mislead you a little bit too. There is a problem with the last
trigger that I posted. When the CASE expression evaluates to a DELETE action
,
the SELECT that results will be empty, because the inserted virtual table is
empty on DELETE transactions.
CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @.insertedcount int
DECLARE @.deletedcount int
SELECT @.insertedcount = COUNT(*) FROM inserted
SELECT @.deletedcount = COUNT(*) FROM deleted
IF (@.insertedcount = 0)
BEGIN
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER), 'DELETE', DELETED.* FROM Inserted
END
IF (@.deletedcount = 0)
BEGIN
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER), 'INSERT', INSERTED.* FROM Inserted
END
IF (@.insertedcount = @.deletedcount)
BEGIN
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER), 'UPDATE', INSERTED.* FROM Inserted
END
END
"CSHARPITPRO" wrote:
> Thanks Mark,
> You have really helped me today!
> "Mark Williams" wrote:
>

determine the SP level of a client install?

Hello,
How can I tell what service pack has been installed on a local (non-server) client installation of SQL Server 2000?
I'd appreciate any feedback
Thanks,
John
Have you looked at Help | About within Query Analyzer?
--=20
Keith
"John P" <John P@.discussions.microsoft.com> wrote in message =
news:6A79F645-CBF8-4123-8FC8-CC0A8952A7F6@.microsoft.com...
> Hello,
>=20
> How can I tell what service pack has been installed on a local =
(non-server) client installation of SQL Server 2000?
>=20
> I'd appreciate any feedback
>=20
> Thanks,
>=20
> John
|||ahh...why didn't I think of that?! I tried that in Enterprise Manager but noticed it was MMC not SQL so I gave up!
Thanks Keith!
"Keith Kratochvil" wrote:

> Have you looked at Help | About within Query Analyzer?
> --
> Keith
>
> "John P" <John P@.discussions.microsoft.com> wrote in message news:6A79F645-CBF8-4123-8FC8-CC0A8952A7F6@.microsoft.com...
>
|||Yeah, Help | About within EM gives information about MMC, not about the =
specific snap in.
--=20
Keith
"John P" <JohnP@.discussions.microsoft.com> wrote in message =
news:62578D1D-7A60-440B-99A4-5B8B774A6E73@.microsoft.com...
> ahh...why didn't I think of that?! I tried that in Enterprise Manager =
but noticed it was MMC not SQL so I gave up![vbcol=seagreen]
>=20
> Thanks Keith!
>=20
> "Keith Kratochvil" wrote:
>=20
news:6A79F645-CBF8-4123-8FC8-CC0A8952A7F6@.microsoft.com...[vbcol=seagreen]
(non-server) client installation of SQL Server 2000?[vbcol=seagreen]

Determine the size of an Image datatype

Is there any method to find out the size of an image datatype currently in the database. For example I have 2 tables that contain the same image, but the archive table is not displaying the same as the main. I am thinking the image file was corrupted somehow but don't know how to check this out.
Any ideas?DataLength() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_4ep4.asp)

-PatP

Determine the last time SP was executed

Hello -

I am trying to determine the last time a SP was executed. Does anyone
know how to do this? I'm trying to cleanup some databases.

Thanks!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You need to be running PROFILER...which would be a big drain to run it 100% in
parallel with a production server...

I suggest running it for a day or 2, during days when you know every part of an
application will get 'touched'. Then investigate the profiler output for your
target objects.|||I have a SP usage table that I use to see what SP's are being ran and
when. The table consists of an ID (autogen), sp_name, LastTimeUsed,
TimesUsed. I can keep track of what SP's are being used, when they
were last used and how many times they were used. I did this one day
as a CYA measure, and found it very useful. And the overhead for my
system is very small.

dcmfan@.aol.comSPNOAM (DCM Fan) wrote in message news:<20031210191602.04241.00000665@.mb-m29.aol.com>...
> You need to be running PROFILER...which would be a big drain to run it 100% in
> parallel with a production server...
> I suggest running it for a day or 2, during days when you know every part of an
> application will get 'touched'. Then investigate the profiler output for your
> target objects.|||Thanks!

Thats what I thought but I was hoping there was somthing else.

Thanks Again!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql

Determine the database objects' sizes?

I have a database whose size is over 50GB. What is the easy way to determine
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FLThis uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||FLX
1)sp_spaceused in the BOL
--
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
****************************************
************************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AN
D
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space u
sed (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric
(15,2),SUM(i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
--
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
****************************************
************************************[vbc
ol=seagreen]
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2
AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space
used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))[/vbcol]
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric
(15,2),SUM(i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =
0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL

> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>

Determine the database objects' sizes?

I have a database whose size is over 50GB. What is the easy way to determine
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FLThis uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||FLX
1)sp_spaceused in the BOL
--
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
****************************************************************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
--
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
****************************************************************************
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> > I have a database whose size is over 50GB. What is the easy way to
> determine
> > the size of each database objects (tables)? I want to find out the ones
> that
> > take most of the space. Thanks a lot,
> >
> > FL
> >
> >
> >
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> > I have a database whose size is over 50GB. What is the easy way to
> determine
> > the size of each database objects (tables)? I want to find out the ones
> that
> > take most of the space. Thanks a lot,
> >
> > FL
> >
> >
> >
>

Determine the database objects' sizes?

I have a database whose size is over 50GB. What is the easy way to determine
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FL
This uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>
|||FLX
1)sp_spaceused in the BOL
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
************************************************** **************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM (i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>
|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
************************************************** **************************
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM (i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =
0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL

> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>

Determine the database name

How do I determine the name of the database I am currently in ?

Thanks

Avi

select d.[name] from sys.sysprocesses p
join sys.databases d on d.database_id = p.dbid
where p.spid = @.@.spid|||

select d.[name] from master..sysprocesses p
join master..sysdatabases d on d.dbid = p.dbid
where p.spid = @.@.spid

Thanks

|||

Hi,

You can use db_name() for getting the name of the database

select db_name()

Eralper

http://www.kodyaz.com

Determine the business day

If I have a calendar table, that has the business days of the entire year ie 2007.--How do I assign it a business day number for eg JUNE 2007 has 21 business days.. Hence on my calendar tbl, I have 21 records for June.. My question is:

How do i assign a business day number eg 6-1-07 is BUSDAY1, 6-4-07 Is Busday2, and so on..

Basicallly, I need to run an update on my calendar table, to indicate, what BUSDAY number it is ie 6-4-07 is BUSDAY 2, 6-5-07 IS Busday 3 and so on, for the entire year 2007.

Pl advise.

if you use sql server 2005 then you can use the Row_Number() ..

Code Snippet

Update MyCalendar

Set

BusinessDayNumber = 'BUSDAY' + Cast(data.Number as varchar)

From

(Select Date,Row_Number() OVER(Partition By Year,Month Order By Date) From MyCalendar) as Data

Where

Data.Date = MyCalendar.Date

if you use SQL Server 2000,

Code Snippet

Update MyCalendar

Set

BusinessDayNumber = (

Select 'BUSDAY' + Cast(Count(*) as Varchar) From MyCalendar Sub

Where

MyCalendar.Year = Sub.Year

and MyCalendar.Month = Sub.Month

and Sub.Date <= MyCalendar.Date

)

|||

Hi Tarana,

Which version of SS are you using?

-- 2005

;with cte

(

select

[date], BSnumber,

row_number() over(partition by year([date]), month([date]) order by [date]) as rn

from

dbo.calendar

where

IsBusinessDay = 1

)

update cte

set BDnumber = rn

-- 2000 / 2005

update dbo.calendar

set BDnumber = (

select

count(*)

from

dbo.calendar as c

where

year(c.[date]) = year(dbo.calendar.[date])

and month(c.[date]) = month(dbo.calendar.[date])

and c.[date] <= dbo.calendar.[date]

and c.IsBusinessDay = 1

)

where IsBusinessDay = 1

AMB

|||

You can determine the Day of the Week very easily:

selectdatepart(weekday,) from

Depending on the settings this will return 1 for sunday, 2 for Monday, 3 for Wednesday etc.

So:

Code Snippet

selectdatepart(weekday,<DateField>)-1 as [WorkDay]

from<TableName>

wheredatepart(weekday,<DateField>)between 2 and 6

|||THis code worked beautiful.sql

determine the assigned identity range

Is there any way to find out a subscriber's assigned identity range (at the
publisher)?
It would be helpful to have an idea where a row was added from its identity
value.
No, it only maintains the last range assigned - which will be the highest
one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<tedcorpus@.hotmail.com> wrote in message
news:%23F7cc61eFHA.412@.tk2msftngp13.phx.gbl...
> Is there any way to find out a subscriber's assigned identity range (at
the
> publisher)?
> It would be helpful to have an idea where a row was added from its
identity
> value.
>
|||In the distribution database, MSrepl_identity_range holds the next ranges,
so You'll have to work with the metadata tables on the subscribers for this:
You could use this type of query:
SELECT sysobjects.name AS TableName, *
FROM MSrepl_identity_range INNER JOIN
sysobjects ON MSrepl_identity_range.objid =
sysobjects.id
The data from each subscriber could be amalgamated at the publisher using
linked servers,
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks!
So I could compare max_identity/current_max columns across servers
(accounting for the range value). But linking the servers just for this
purpose might be more administration work than benefit at this point.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ehDGtl7eFHA.2076@.TK2MSFTNGP15.phx.gbl...
> In the distribution database, MSrepl_identity_range holds the next ranges,
> so You'll have to work with the metadata tables on the subscribers for
> this:
> You could use this type of query:
> SELECT sysobjects.name AS TableName, *
> FROM MSrepl_identity_range INNER JOIN
> sysobjects ON MSrepl_identity_range.objid =
> sysobjects.id
> The data from each subscriber could be amalgamated at the publisher using
> linked servers,
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||You only need to look at the max value on the publisher's distribution
database. That will be the next assigned range.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"TCorp" <tcorpus@.hotmail.com> wrote in message
news:eg1MgYagFHA.2548@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Thanks!
> So I could compare max_identity/current_max columns across servers
> (accounting for the range value). But linking the servers just for this
> purpose might be more administration work than benefit at this point.
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:ehDGtl7eFHA.2076@.TK2MSFTNGP15.phx.gbl...
ranges,[vbcol=seagreen]
using
>

determine tables that are Simple recovery

I need a sql statment to return a list of tables for a given database
where the Recovery Model option is Simple.
TIA
Rob"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1129311952.833612.171530@.g14g2000cwa.googlegr oups.com...
>I need a sql statment to return a list of tables for a given database
> where the Recovery Model option is Simple.
> TIA
> Rob

The recovery model is a database-wide setting. There is no recovery model
option for individual tables.

You can determine the recovery model like this:

SELECT DATABASEPROPERTYEX('database_name', 'RECOVERY')

--
David Portas
SQL Server MVP
--|||Better Question. How can it tell the databases in my sql instance that
have a recovery model of 'Simple'?
Hope this makes sense now
Thanks|||Erp! Yes, replace(question,'Tables','Database')
Thanks!|||"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1129328233.934256.231310@.g49g2000cwa.googlegr oups.com...
> Better Question. How can it tell the databases in my sql instance that
> have a recovery model of 'Simple'?
> Hope this makes sense now
> Thanks

SELECT catalog_name
FROM information_schema.schemata
WHERE DATABASEPROPERTYEX(catalog_name,'RECOVERY') = 'SIMPLE' ;

--
David Portas
SQL Server MVP
--|||Thank you very much! Ill be able to use this in my backup using SQLsafe
when I perform a log backup. I kept getting errors when it tried to
backup databases with simple model. Now I can skip them.

Determine Table's PK Columns

I want to do a query on a db's system tables to determine what column(s) are
part of the primary key.
Example.
Code:
SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
sysobjects.name='tablename'
Yields all the column names. But I just want to see those that are part of
the primary key. How?
Ryan
> SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
> sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
> sysobjects.name='tablename'
shuldn't you use information_schema?
.~. Might, Courage, Vision. Sincerity. http://www.linux-sxs.org
/ v \
/( _ )\ (Ubuntu 5.10) Linux 2.6.14.3
^ ^ 22:37:07 up 9 days 2:31 load average: 3.12 1.63 0.80|||Try:
select
*
from
information_schema.constraint_column_usage
where
table_name = 'Order Details'
and objectproperty (object_id (constraint_name), 'IsPrimaryKey') = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:037F8848-C843-4A77-B385-6994E6235CDA@.microsoft.com...
>I want to do a query on a db's system tables to determine what column(s)
>are
> part of the primary key.
> Example.
> Code:
> SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
> sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
> sysobjects.name='tablename'
> Yields all the column names. But I just want to see those that are part of
> the primary key. How?
> Ryan|||Thank you both of you. Yes, Man-wai Chang, the information_schema looks lik
e
it's a lot easier than joining the system tables like I was doing. Thank
you, both.

Determine table size

Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica
Hi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:

> Hi,
> I have a client with a database that is running over 1G in size. Looking at
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>
|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica
|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>
|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...[vbcol=seagreen]
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>

Thursday, March 29, 2012

Blog Archive

About Me