How to schedule DBCC INDEXDEFRAG the best way

HI
I would like to optimize indexes on two databases dbA and
dbB. The optimization must be able to be configured by a
non DBA, with a script run after the installation of the
SQL Server and the user applications. The job has to be
scheduled.
Since the requirements are 24*7 access to all tables I
cannot (?)use DBCC REINDEX, which - what I understand -
should give me the best optimization of my existing
indexes regarding diskstructure etc., because it places
locks on the tables while running.
The next best thing must then - to my knowlegde - be DBCC
INDEXDEFRAG. (Note: Update of statistics are handled!)
Unfortunately I have some troubles scheduling DBCC
INDEXDEFRAG using a job.
The index optimization configured by the Maintenance Plan
Wizard uses DBCC REINDEX so that is out of the question,
except if xp_sqlmaint has another swith that can be used
instead of -RebldIdx (I cannot find an "indexdefrag
switch" it in BOL)'
Then I build a script which explicitely mentioned every
table and every index of both dbA and dbB to be optimized.
The script looked like this:
IF EXISTS (SELECT name FROM sysindexes WHERE name
= 'PK_ACT')
DBCC INDEXDEFRAG ('dbA', [ACT],[PK_ACT])
IF EXISTS (SELECT name FROM sysindexes WHERE name
= 'PK_ACT_ASSOC')
DBCC INDEXDEFRAG ('dbA', [ACT_ASSOC],[PK_ACT_ASSOC])
etc....
Unfortunalty, the script could not be pasted in the
command box of the job step since it migth have been to
long (670 linex approx.)
I of course could place the script externally in the file
system and have a call to osql.exe scheduled, but I would
rather have the DBCC INDEXDEFRAG statement:
- placed "internally" in the SQL Server rather than in the
file system.
- be able to make a call to DBCC INDEXDEFRAG without
having to specify each and every table/index to be
optimized, but - in the same way that DBCC REINDEX are
handled by the Maintenance Plan Wizard - just
specify: "these are the databases I want to process, now
do it for me!"
I captured and tweaked a script (bottom of message) to
setup the maintenance plan and job it runs okay, but with
DBCC REINDEX instead of DBCC INDEXDEFRAG:
The interesting part is this:
SET @.strMaintenanceCommand = 'EXECUTE
master.dbo.xp_sqlmaint ''-PlanID ' + convert(varchar
(256),@.MaintenancePlanID) + ' -WriteHistory -RebldIdx
100'''
If only "-RebldIdx" could be replaced with something else.
Could it'
yours truly
Jakob Persson
********************SCRIPT*************************''
USE MSDB
Go
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.MaintenancePlanID UNIQUEIDENTIFIER
DECLARE @.ReturnCode INT
DECLARE @.SQL VARCHAR(2000)
DECLARE @.strMaintenanceCommand VARCHAR(2000)
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM [msdb].[dbo].[sysdbmaintplans]
WHERE [msdb].[dbo].[sysdbmaintplans].[plan_name] = N'INDEXDEFRAG') >= 1
BEGIN
SET @.MaintenancePlanID = (
SELECT [plan_id]
FROM [msdb].[dbo].[sysdbmaintplans]
WHERE [msdb].[dbo].[sysdbmaintplans].[plan_name]
= 'INDEXDEFRAG'
)
SET @.SQL = 'EXEC sp_delete_maintenance_plan '
+ '''' + convert(varchar(256),@.MaintenancePlanID) + ''''
EXECUTE(@.SQL)
END
SET @.MaintenancePlanID = NULL
EXEC sp_add_maintenance_plan @.plan_name
= 'INDEXDEFRAG', @.plan_id = @.MaintenancePlanID OUTPUT
EXEC sp_add_maintenance_plan_db @.plan_id = @.MaintenancePlanID , @.db_name = 'DACCIS01'
EXEC sp_add_maintenance_plan_db @.plan_id = @.MaintenancePlanID , @.db_name = 'MIRROR'
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name
= N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Database
Maintenance'
-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'DACCIS01 and
MIRROR ''INDEXDEFRAG''')
IF (@.JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <>
0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''DACCIS01 and
MIRROR ''INDEXDEFRAG'''' since there is already a multi-
server job with this name.', 16, 1)
-- GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @.job_name = N'DACCIS01
and MIRROR ''INDEXDEFRAG'''
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT , @.job_name = N'DACCIS01 and
MIRROR ''INDEXDEFRAG''', @.owner_login_name = N'SA',
@.description = N'No description available.',
@.category_name = N'Database Maintenance', @.enabled = 1,
@.notify_level_email = 0, @.notify_level_page = 0,
@.notify_level_netsend = 0, @.notify_level_eventlog = 2,
@.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
QuitWithRollback
-- Add the job steps
SET @.strMaintenanceCommand = 'EXECUTE
master.dbo.xp_sqlmaint ''-PlanID ' + convert(varchar
(256),@.MaintenancePlanID) + ' -WriteHistory -RebldIdx
100'''
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep
@.job_id = @.JobID,
@.step_id = 1,
@.step_name
= N'Step 1',
@.command = @.strMaintenanceCommand,
@.database_name = N'master',
@.server = N'',
@.database_user_name = N'',
@.subsystem
= N'TSQL',
@.cmdexec_success_code = 0,
@.flags = 4,
@.retry_attempts = 0,
@.retry_interval = 0,
@.output_file_name = N'',
@.on_success_step_id = 0,
@.on_success_action = 1,
@.on_fail_step_id = 0,
@.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID, @.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
QuitWithRollback
-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule
@.job_id = @.JobID, @.name = N'Schedule 1', @.enabled = 1,
@.freq_type = 4, @.active_start_date = 20031212,
@.active_start_time = 10000, @.freq_interval = 1,
@.freq_subday_type = 8, @.freq_subday_interval = 2,
@.freq_relative_interval = 0, @.freq_recurrence_factor = 0,
@.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id
= @.JobID, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:Jakob,
> I of course could place the script externally in the file
> system and have a call to osql.exe scheduled, but I would
> rather have the DBCC INDEXDEFRAG statement:
> - placed "internally" in the SQL Server rather than in the
> file system.
OSQL.EXE is not any more or less "internal" than SQL Server Agent. both are
client applications which logs on to SQL Server and executes SQL commands.
> If only "-RebldIdx" could be replaced with something else.
> Could it'
Xp_sqlmaint is basically just a wrapper around sqlmaint.exe, where the
sqlmaint.exe logs on to SQL Server and execute the commands as specified by
the command-line parameters. Unfortunately, there's no command-line
parameter which makes the exe execute DBCC INDEXDEFRAG commands (to the best
of my knowledge, supported by Books Online).
My suggestion is that you write a stored procedure which has first a cursor
that uses the sysobjects or INFORMATION_SCHEM.TABLES to for each table in a
cursor. Inside this cursor, you have another cursor (another loop) where you
use sysindexes and loop each index. Inside this second loop, you now have
both table and index name, so you can construct and execute the DBCC
INDEXDEFRAG command. Just make sure that you in the cursor definition
exclude system tables, statistics etc (use functions such as OBJECTPROPERTY,
INDERPROPERTY etc).
You can probably write this as an sp_ and put it in master. Then have your
own table in which you store the database name for the databases you want to
defrag (write a simple UI if this need to be configurable for an end-user).
Then in your job, you have a cursor which loop this table with database
names in a cursor (having dbname in a variable, @.db), and execute:
EXEC @.db..sp_DefragAllTable --or whatever you called the proc
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jakob Persson" <jakobpersson@.yahoo.dk> wrote in message
news:019801c3c160$b0f763d0$a001280a@.phx.gbl...
> HI
> I would like to optimize indexes on two databases dbA and
> dbB. The optimization must be able to be configured by a
> non DBA, with a script run after the installation of the
> SQL Server and the user applications. The job has to be
> scheduled.
> Since the requirements are 24*7 access to all tables I
> cannot (?)use DBCC REINDEX, which - what I understand -
> should give me the best optimization of my existing
> indexes regarding diskstructure etc., because it places
> locks on the tables while running.
> The next best thing must then - to my knowlegde - be DBCC
> INDEXDEFRAG. (Note: Update of statistics are handled!)
> Unfortunately I have some troubles scheduling DBCC
> INDEXDEFRAG using a job.
> The index optimization configured by the Maintenance Plan
> Wizard uses DBCC REINDEX so that is out of the question,
> except if xp_sqlmaint has another swith that can be used
> instead of -RebldIdx (I cannot find an "indexdefrag
> switch" it in BOL)'
> Then I build a script which explicitely mentioned every
> table and every index of both dbA and dbB to be optimized.
> The script looked like this:
> IF EXISTS (SELECT name FROM sysindexes WHERE name
> = 'PK_ACT')
> DBCC INDEXDEFRAG ('dbA', [ACT],[PK_ACT])
> IF EXISTS (SELECT name FROM sysindexes WHERE name
> = 'PK_ACT_ASSOC')
> DBCC INDEXDEFRAG ('dbA', [ACT_ASSOC],[PK_ACT_ASSOC])
> etc....
> Unfortunalty, the script could not be pasted in the
> command box of the job step since it migth have been to
> long (670 linex approx.)
> I of course could place the script externally in the file
> system and have a call to osql.exe scheduled, but I would
> rather have the DBCC INDEXDEFRAG statement:
> - placed "internally" in the SQL Server rather than in the
> file system.
> - be able to make a call to DBCC INDEXDEFRAG without
> having to specify each and every table/index to be
> optimized, but - in the same way that DBCC REINDEX are
> handled by the Maintenance Plan Wizard - just
> specify: "these are the databases I want to process, now
> do it for me!"
>
> I captured and tweaked a script (bottom of message) to
> setup the maintenance plan and job it runs okay, but with
> DBCC REINDEX instead of DBCC INDEXDEFRAG:
> The interesting part is this:
> SET @.strMaintenanceCommand = 'EXECUTE
> master.dbo.xp_sqlmaint ''-PlanID ' + convert(varchar
> (256),@.MaintenancePlanID) + ' -WriteHistory -RebldIdx
> 100'''
> If only "-RebldIdx" could be replaced with something else.
> Could it'
> yours truly
> Jakob Persson
> ********************SCRIPT*************************''
> USE MSDB
> Go
>
> BEGIN TRANSACTION
> DECLARE @.JobID BINARY(16)
> DECLARE @.MaintenancePlanID UNIQUEIDENTIFIER
> DECLARE @.ReturnCode INT
> DECLARE @.SQL VARCHAR(2000)
> DECLARE @.strMaintenanceCommand VARCHAR(2000)
> SELECT @.ReturnCode = 0
> IF (SELECT COUNT(*) FROM [msdb].[dbo].[sysdbmaintplans]
> WHERE [msdb].[dbo].[sysdbmaintplans].[plan_name] => N'INDEXDEFRAG') >= 1
> BEGIN
> SET @.MaintenancePlanID = (
> SELECT [plan_id]
> FROM [msdb].[dbo].[sysdbmaintplans]
> WHERE [msdb].[dbo].[sysdbmaintplans].[plan_name]
> = 'INDEXDEFRAG'
> )
> SET @.SQL = 'EXEC sp_delete_maintenance_plan '
> + '''' + convert(varchar(256),@.MaintenancePlanID) + ''''
> EXECUTE(@.SQL)
> END
> SET @.MaintenancePlanID = NULL
> EXEC sp_add_maintenance_plan @.plan_name
> = 'INDEXDEFRAG', @.plan_id = @.MaintenancePlanID OUTPUT
> EXEC sp_add_maintenance_plan_db @.plan_id => @.MaintenancePlanID , @.db_name = 'DACCIS01'
> EXEC sp_add_maintenance_plan_db @.plan_id => @.MaintenancePlanID , @.db_name = 'MIRROR'
>
> IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name
> = N'Database Maintenance') < 1
> EXECUTE msdb.dbo.sp_add_category @.name = N'Database
> Maintenance'
> -- Delete the job with the same name (if it exists)
> SELECT @.JobID = job_id
> FROM msdb.dbo.sysjobs
> WHERE (name = N'DACCIS01 and
> MIRROR ''INDEXDEFRAG''')
> IF (@.JobID IS NOT NULL)
> BEGIN
> -- Check if the job is a multi-server job
> IF (EXISTS (SELECT *
> FROM msdb.dbo.sysjobservers
> WHERE (job_id = @.JobID) AND (server_id <>
> 0)))
> BEGIN
> -- There is, so abort the script
> RAISERROR (N'Unable to import job ''DACCIS01 and
> MIRROR ''INDEXDEFRAG'''' since there is already a multi-
> server job with this name.', 16, 1)
> -- GOTO QuitWithRollback
> END
> ELSE
> -- Delete the [local] job
> EXECUTE msdb.dbo.sp_delete_job @.job_name = N'DACCIS01
> and MIRROR ''INDEXDEFRAG'''
> SELECT @.JobID = NULL
> END
>
> BEGIN
> -- Add the job
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id => @.JobID OUTPUT , @.job_name = N'DACCIS01 and
> MIRROR ''INDEXDEFRAG''', @.owner_login_name = N'SA',
> @.description = N'No description available.',
> @.category_name = N'Database Maintenance', @.enabled = 1,
> @.notify_level_email = 0, @.notify_level_page = 0,
> @.notify_level_netsend = 0, @.notify_level_eventlog = 2,
> @.delete_level= 0
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
> QuitWithRollback
>
> -- Add the job steps
> SET @.strMaintenanceCommand = 'EXECUTE
> master.dbo.xp_sqlmaint ''-PlanID ' + convert(varchar
> (256),@.MaintenancePlanID) + ' -WriteHistory -RebldIdx
> 100'''
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep
> @.job_id = @.JobID,
> @.step_id => 1,
> @.step_name
> = N'Step 1',
> @.command => @.strMaintenanceCommand,
>
> @.database_name = N'master',
> @.server => N'',
>
> @.database_user_name = N'',
> @.subsystem
> = N'TSQL',
>
> @.cmdexec_success_code = 0,
> @.flags => 4,
>
> @.retry_attempts = 0,
>
> @.retry_interval = 0,
>
> @.output_file_name = N'',
>
> @.on_success_step_id = 0,
>
> @.on_success_action = 1,
>
> @.on_fail_step_id = 0,
>
> @.on_fail_action = 2
>
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
> QuitWithRollback
> EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id => @.JobID, @.start_step_id = 1
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
> QuitWithRollback
> -- Add the job schedules
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule
> @.job_id = @.JobID, @.name = N'Schedule 1', @.enabled = 1,
> @.freq_type = 4, @.active_start_date = 20031212,
> @.active_start_time = 10000, @.freq_interval = 1,
> @.freq_subday_type = 8, @.freq_subday_interval = 2,
> @.freq_relative_interval = 0, @.freq_recurrence_factor = 0,
> @.active_end_date = 99991231, @.active_end_time = 235959
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
> QuitWithRollback
> -- Add the Target Servers
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id
> = @.JobID, @.server_name = N'(local)'
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
> QuitWithRollback
> END
> COMMIT TRANSACTION
> GOTO EndSave
> QuitWithRollback:
> IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
> EndSave:

How to run query between two databases

Hi i have query which i need to join it
like i am doing some thing like
Select [a].[dbo].[tbla].* From [a].[dbo].[tbla] INNER INNER JOIN
[B].[dbo].[tblB ON [a].[dbo].[tbla].[ID] =
[b].[dbo].[tbla].[ID]
and i am running that query in database A but the only problem is it saying
that b.dbo.tblb.ID is invalid colum name i dont understand that i am giving
full address of both databases but cannt do it ..... any help thanksYour query seem to be syntactically wrong.
Anyways, correct it and try to add table alias for the tables and use them
to refer to the columns.
Or post the actual query that gave the error you mentioned.
--
"amjad" wrote:

> Hi i have query which i need to join it
> like i am doing some thing like
> Select [a].[dbo].[tbla].* From [a].[dbo].[tbla] INNER INNER JOIN
> [B].[dbo].[tblB ON [a].[dbo].[tbla].[ID] =
> [b].[dbo].[tbla].[ID]
> and i am running that query in database A but the only problem is it sayin
g
> that b.dbo.tblb.ID is invalid colum name i dont understand that i am givin
g
> full address of both databases but cannt do it ..... any help thanks|||could you post what your actually doing, rather than something like
what you're doing?
I mean what's an INNER INNER JOIN? is it something that's really
seriously inner on the join? and is it a typo or have you missed out a
"]" in your actual code?|||thanks for help i solved thanks
"Will" wrote:

> could you post what your actually doing, rather than something like
> what you're doing?
> I mean what's an INNER INNER JOIN? is it something that's really
> seriously inner on the join? and is it a typo or have you missed out a
> "]" in your actual code?
>

How to run a script against all databases

I want to run a script to get the file details on each database.
Script:
Use DBname
Select * from sysfiles
I want to cycle through the databases using dbname as variable. I tried a
bunch of different ways with no luck. Any help or pointers would be great.
What I'm looking to do is "inventory" my sql servers. I need to come up
with a Disaster Recovery Plan and I have 10 + sql servers I need to document.
I would like to come up with a script (or scripts) that will give me list of
databases and details on databases for that server. I would like to dump
into table and eventually use that to keep track of my servers. Any
thoughts ?
thanks in advance
rob
Try this:
sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
"Rob" wrote:

> I want to run a script to get the file details on each database.
> Script:
> Use DBname
> Select * from sysfiles
> I want to cycle through the databases using dbname as variable. I tried a
> bunch of different ways with no luck. Any help or pointers would be great.
> What I'm looking to do is "inventory" my sql servers. I need to come up
> with a Disaster Recovery Plan and I have 10 + sql servers I need to document.
> I would like to come up with a script (or scripts) that will give me list of
> databases and details on databases for that server. I would like to dump
> into table and eventually use that to keep track of my servers. Any
> thoughts ?
> thanks in advance
> rob
|||This works great... yet another undocumented MS trick.
One question. One of my tables has a name that starts with a number, 3.
This seems to choke the script. Any ideas on how to resolve.
thanks again for great tip.
rob
"CLM" wrote:
[vbcol=seagreen]
> Try this:
> sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
> "Rob" wrote:
|||Surround the question mark in square brackets [?]
A number is not a valid starting character for a regular identifier.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:BC0CA6B4-0A30-4D45-89F7-EC46DCB1B15A@.microsoft.com...[vbcol=seagreen]
> This works great... yet another undocumented MS trick.
> One question. One of my tables has a name that starts with a number, 3.
> This seems to choke the script. Any ideas on how to resolve.
> thanks again for great tip.
> rob
>
> "CLM" wrote:

How to run a script against all databases

I want to run a script to get the file details on each database.
Script:
Use DBname
Select * from sysfiles
I want to cycle through the databases using dbname as variable. I tried a
bunch of different ways with no luck. Any help or pointers would be great.
What I'm looking to do is "inventory" my sql servers. I need to come up
with a Disaster Recovery Plan and I have 10 + sql servers I need to document.
I would like to come up with a script (or scripts) that will give me list of
databases and details on databases for that server. I would like to dump
into table and eventually use that to keep track of my servers. Any
thoughts '
thanks in advance
robTry this:
sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
"Rob" wrote:
> I want to run a script to get the file details on each database.
> Script:
> Use DBname
> Select * from sysfiles
> I want to cycle through the databases using dbname as variable. I tried a
> bunch of different ways with no luck. Any help or pointers would be great.
> What I'm looking to do is "inventory" my sql servers. I need to come up
> with a Disaster Recovery Plan and I have 10 + sql servers I need to document.
> I would like to come up with a script (or scripts) that will give me list of
> databases and details on databases for that server. I would like to dump
> into table and eventually use that to keep track of my servers. Any
> thoughts '
> thanks in advance
> rob|||This works great... yet another undocumented MS trick.
One question. One of my tables has a name that starts with a number, 3.
This seems to choke the script. Any ideas on how to resolve.
thanks again for great tip.
rob
"CLM" wrote:
> Try this:
> sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
> "Rob" wrote:
> > I want to run a script to get the file details on each database.
> > Script:
> >
> > Use DBname
> > Select * from sysfiles
> >
> > I want to cycle through the databases using dbname as variable. I tried a
> > bunch of different ways with no luck. Any help or pointers would be great.
> > What I'm looking to do is "inventory" my sql servers. I need to come up
> > with a Disaster Recovery Plan and I have 10 + sql servers I need to document.
> > I would like to come up with a script (or scripts) that will give me list of
> > databases and details on databases for that server. I would like to dump
> > into table and eventually use that to keep track of my servers. Any
> > thoughts '
> >
> > thanks in advance
> > rob|||Surround the question mark in square brackets [?]
A number is not a valid starting character for a regular identifier.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:BC0CA6B4-0A30-4D45-89F7-EC46DCB1B15A@.microsoft.com...
> This works great... yet another undocumented MS trick.
> One question. One of my tables has a name that starts with a number, 3.
> This seems to choke the script. Any ideas on how to resolve.
> thanks again for great tip.
> rob
>
> "CLM" wrote:
>> Try this:
>> sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
>> "Rob" wrote:
>> > I want to run a script to get the file details on each database.
>> > Script:
>> >
>> > Use DBname
>> > Select * from sysfiles
>> >
>> > I want to cycle through the databases using dbname as variable. I
>> > tried a
>> > bunch of different ways with no luck. Any help or pointers would be
>> > great.
>> > What I'm looking to do is "inventory" my sql servers. I need to come
>> > up
>> > with a Disaster Recovery Plan and I have 10 + sql servers I need to
>> > document.
>> > I would like to come up with a script (or scripts) that will give me
>> > list of
>> > databases and details on databases for that server. I would like to
>> > dump
>> > into table and eventually use that to keep track of my servers. Any
>> > thoughts '
>> >
>> > thanks in advance
>> > rob

How to run a script against all databases

I want to run a script to get the file details on each database.
Script:
Use DBname
Select * from sysfiles
I want to cycle through the databases using dbname as variable. I tried a
bunch of different ways with no luck. Any help or pointers would be great.
What I'm looking to do is "inventory" my sql servers. I need to come up
with a Disaster Recovery Plan and I have 10 + sql servers I need to document
.
I would like to come up with a script (or scripts) that will give me list of
databases and details on databases for that server. I would like to dump
into table and eventually use that to keep track of my servers. Any
thoughts '
thanks in advance
robTry this:
sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
"Rob" wrote:

> I want to run a script to get the file details on each database.
> Script:
> Use DBname
> Select * from sysfiles
> I want to cycle through the databases using dbname as variable. I tried a
> bunch of different ways with no luck. Any help or pointers would be great
.
> What I'm looking to do is "inventory" my sql servers. I need to come up
> with a Disaster Recovery Plan and I have 10 + sql servers I need to docume
nt.
> I would like to come up with a script (or scripts) that will give me list
of
> databases and details on databases for that server. I would like to dump
> into table and eventually use that to keep track of my servers. Any
> thoughts '
> thanks in advance
> rob|||This works great... yet another undocumented MS trick.
One question. One of my tables has a name that starts with a number, 3.
This seems to choke the script. Any ideas on how to resolve.
thanks again for great tip.
rob
"CLM" wrote:
[vbcol=seagreen]
> Try this:
> sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
> "Rob" wrote:
>|||Surround the question mark in square brackets [?]
A number is not a valid starting character for a regular identifier.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:BC0CA6B4-0A30-4D45-89F7-EC46DCB1B15A@.microsoft.com...[vbcol=seagreen]
> This works great... yet another undocumented MS trick.
> One question. One of my tables has a name that starts with a number, 3.
> This seems to choke the script. Any ideas on how to resolve.
> thanks again for great tip.
> rob
>
> "CLM" wrote:
>

how to return all store procedure names from a databases?

Hi all,

I require a script to get all store procedure names from a database. I managed to find a script on how to return all the tables names from a db . I was thinking there could be script that could do the same thing but instead it returns the sp names

Thanks

Matthew

One way would be to search the sysobjects table:

select name
from sysobjects
where type = 'P'
order by name

Another (better) alternative is:

select routine_name
from information_schema.routines
where routine_type = 'PROCEDURE'
order by routine_name

|||

With SQL 2005, use:

SELECT Name
FROM sys.procedures

|||

The examples below will return all non-system stored procedures.

Chris

--SQL Server 2000
SELECT [name]
FROM dbo.sysobjects
WHERE OBJECTPROPERTY([id], 'IsProcedure') = 1
AND OBJECTPROPERTY([id], 'IsMSShipped') = 0

--SQL Server 2005
SELECT [name]
FROM sys.procedures
WHERE OBJECTPROPERTY([object_id], 'IsMSShipped') = 0

How to restrict users to particular databases

While web hosting I use Sql Server 2000 as the database. Imagine I have hosted 3 Web Sites. All these 3 users want to modify/update their designs. What I did is I created respective 3 users having access to the respective databases only. So that they can registerd the ip and add to the Sql Server 2000 Enterprise Manager. These users are able to access their own databases only. But all these 3 users are able to access the default databases like master,pubs etc. How to restrict this.

Also suggest which is the optimal way to give control to the respective users while using Sql Server 2000.

====Suresh, P.R, Postal Training Centre, Mysore.

Hi,

Pubs is a samples database, for the rest have a look on:

http://groups.google.de/group/microsoft.public.sqlserver.security/browse_frm/thread/b4f926814e2678e9

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||... and on production servers it is best to drop PUBS & NorthWind databases.

How to Restrict all SQL Databases Size

Hello -

I have over 100 MSSQL Databases on my SQL SERVER. How do I restrict
all the MSSQL databases and its transaction logs to 100 MB.

Can someone help me with any script which will do that.

Thanks,

Rubal Jain
www.Rubal.netHi

This will depend on how/what these databases are and what you want to set
the sizes to.
A start could be the script created by:

EXEC master..sp_MSForEachdb 'USE ? SELECT ''ALTER DATABASE '' + db_name() +
'' MODIFY FILE ( name= '' + RTRIM(name) + '', MAXSIZE=200)'' FROM sysfiles
WHERE status & 0x40 <> 0x40 '

John

"Rubal Jain" <rubaljain@.yahoo.com> wrote in message
news:7a30b199.0407150445.56b2a480@.posting.google.c om...
> Hello -
> I have over 100 MSSQL Databases on my SQL SERVER. How do I restrict
> all the MSSQL databases and its transaction logs to 100 MB.
> Can someone help me with any script which will do that.
> Thanks,
> Rubal Jain
> www.Rubal.net