restrict access to database: indexes

Showing posts with label indexes. Show all posts

Friday, March 30, 2012

How to script index in 2005

sql 2005
right click on table to script - create script does not include indexes, or
perms. how to include?
any help appreciated
thanks
chris
Hi,
use the script wizard and enable the option "Include Script Indexes".
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Jens
> use the script wizard and enable the option "Include Script Indexes".
I thought it will be able in SP2 comming soon,isn't it?
If it isn't can yoi point out?
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1168553540.198986.94110@.77g2000hsv.googlegrou ps.com...
> Hi,
> use the script wizard and enable the option "Include Script Indexes".
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

How to script index in 2005

sql 2005
right click on table to script - create script does not include indexes, or
perms. how to include?
any help appreciated
thanks
chrisHi,
use the script wizard and enable the option "Include Script Indexes".
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Jens
> use the script wizard and enable the option "Include Script Indexes".
I thought it will be able in SP2 comming soon,isn't it?
If it isn't can yoi point out?
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1168553540.198986.94110@.77g2000hsv.googlegroups.com...
> Hi,
> use the script wizard and enable the option "Include Script Indexes".
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

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:

Monday, March 12, 2012

How to return all tables that have an index?

Using 2005
I want to generate a list of all tables that have indexes, so I can reindex
them. I don't want to bother doing a reindex on a table that has no
indexes. I thought it would be easy enough to pull this data from
sys.indexes (or something like that), but it is quirky. The following query
will return some tables, showing them as having 1 index, when really they
have NO indexes. But yet other tables result with having 1 index, when that
is correct. And this query also returns dtproperties, which is not one of
my user tables, so that also makes this query incorrect.
This is what I'm using, and maybe someone can help fine tune it so it is
100% accurate... Try this against any of your DBs and you'll see it is not
quite accurate even though mostly accurate...
Select object_name(object_id) as TableName, count(object_id) as NumIndexes,
object_id
from sys.indexes
where object_id>1000 -- eliminate some system table junk below 1000
group by object_id
order by TableNameIf you just want a list of tables that contain indexes, you can do this:
SELECT name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
ORDER BY name;
If you want the tables and the index names, try this variation:
SELECT
t.name, i.name, i.type_desc
FROM sys.indexes AS i INNER JOIN
sys.tables AS t ON t.object_id = i.object_id
WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
ORDER BY t.name
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
> Using 2005
> I want to generate a list of all tables that have indexes, so I can
> reindex
> them. I don't want to bother doing a reindex on a table that has no
> indexes. I thought it would be easy enough to pull this data from
> sys.indexes (or something like that), but it is quirky. The following
> query
> will return some tables, showing them as having 1 index, when really they
> have NO indexes. But yet other tables result with having 1 index, when
> that
> is correct. And this query also returns dtproperties, which is not one
> of
> my user tables, so that also makes this query incorrect.
> This is what I'm using, and maybe someone can help fine tune it so it is
> 100% accurate... Try this against any of your DBs and you'll see it is
> not
> quite accurate even though mostly accurate...
> Select object_name(object_id) as TableName, count(object_id) as
> NumIndexes,
> object_id
> from sys.indexes
> where object_id>1000 -- eliminate some system table junk below 1000
> group by object_id
> order by TableName
>|||Thank you. Why does dtproperties show up as a user table? There was a
known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
user table, but I figured something in SQL 2005 would prevent it from
appearing as a user table.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> If you just want a list of tables that contain indexes, you can do this:
> SELECT name
> FROM sys.tables
> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
> ORDER BY name;
> If you want the tables and the index names, try this variation:
> SELECT
> t.name, i.name, i.type_desc
> FROM sys.indexes AS i INNER JOIN
> sys.tables AS t ON t.object_id = i.object_id
> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
> ORDER BY t.name
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
> > Using 2005
> >
> > I want to generate a list of all tables that have indexes, so I can
> > reindex
> > them. I don't want to bother doing a reindex on a table that has no
> > indexes. I thought it would be easy enough to pull this data from
> > sys.indexes (or something like that), but it is quirky. The following
> > query
> > will return some tables, showing them as having 1 index, when really
they
> > have NO indexes. But yet other tables result with having 1 index, when
> > that
> > is correct. And this query also returns dtproperties, which is not one
> > of
> > my user tables, so that also makes this query incorrect.
> >
> > This is what I'm using, and maybe someone can help fine tune it so it is
> > 100% accurate... Try this against any of your DBs and you'll see it is
> > not
> > quite accurate even though mostly accurate...
> >
> > Select object_name(object_id) as TableName, count(object_id) as
> > NumIndexes,
> > object_id
> > from sys.indexes
> > where object_id>1000 -- eliminate some system table junk below 1000
> > group by object_id
> > order by TableName
> >
> >
>|||> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
I'm not sure. I'll see what I can find out.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
>> If you just want a list of tables that contain indexes, you can do this:
>> SELECT name
>> FROM sys.tables
>> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
>> ORDER BY name;
>> If you want the tables and the index names, try this variation:
>> SELECT
>> t.name, i.name, i.type_desc
>> FROM sys.indexes AS i INNER JOIN
>> sys.tables AS t ON t.object_id = i.object_id
>> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
>> ORDER BY t.name
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "HK" <replywithingroup@.notreal.com> wrote in message
>> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
>> > Using 2005
>> >
>> > I want to generate a list of all tables that have indexes, so I can
>> > reindex
>> > them. I don't want to bother doing a reindex on a table that has no
>> > indexes. I thought it would be easy enough to pull this data from
>> > sys.indexes (or something like that), but it is quirky. The following
>> > query
>> > will return some tables, showing them as having 1 index, when really
> they
>> > have NO indexes. But yet other tables result with having 1 index, when
>> > that
>> > is correct. And this query also returns dtproperties, which is not
>> > one
>> > of
>> > my user tables, so that also makes this query incorrect.
>> >
>> > This is what I'm using, and maybe someone can help fine tune it so it
>> > is
>> > 100% accurate... Try this against any of your DBs and you'll see it is
>> > not
>> > quite accurate even though mostly accurate...
>> >
>> > Select object_name(object_id) as TableName, count(object_id) as
>> > NumIndexes,
>> > object_id
>> > from sys.indexes
>> > where object_id>1000 -- eliminate some system table junk below 1000
>> > group by object_id
>> > order by TableName
>> >
>> >
>>
>|||Go add to Gail's response, you can exclude dtproperties with:
OBJECTPROPERTY(t.object_id, 'IsMsShipped') = 1
Note that this won't work for some other tables, like sysdiagrams. I think
the underlying issue is that there isn't a consistent definition of what a
'user object' is. In other words, should objects created after installation
by Microsoft or third party tools be considered system objects? Perhaps
there should be a 'NonUserObject' OBJECTPROPERTY so that these objects can
be excluded from script.
In any case, I wouldn't expect any harm in including tool-created objects as
part of your normal maintenance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
>> If you just want a list of tables that contain indexes, you can do this:
>> SELECT name
>> FROM sys.tables
>> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
>> ORDER BY name;
>> If you want the tables and the index names, try this variation:
>> SELECT
>> t.name, i.name, i.type_desc
>> FROM sys.indexes AS i INNER JOIN
>> sys.tables AS t ON t.object_id = i.object_id
>> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
>> ORDER BY t.name
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "HK" <replywithingroup@.notreal.com> wrote in message
>> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
>> > Using 2005
>> >
>> > I want to generate a list of all tables that have indexes, so I can
>> > reindex
>> > them. I don't want to bother doing a reindex on a table that has no
>> > indexes. I thought it would be easy enough to pull this data from
>> > sys.indexes (or something like that), but it is quirky. The following
>> > query
>> > will return some tables, showing them as having 1 index, when really
> they
>> > have NO indexes. But yet other tables result with having 1 index, when
>> > that
>> > is correct. And this query also returns dtproperties, which is not
>> > one
>> > of
>> > my user tables, so that also makes this query incorrect.
>> >
>> > This is what I'm using, and maybe someone can help fine tune it so it
>> > is
>> > 100% accurate... Try this against any of your DBs and you'll see it is
>> > not
>> > quite accurate even though mostly accurate...
>> >
>> > Select object_name(object_id) as TableName, count(object_id) as
>> > NumIndexes,
>> > object_id
>> > from sys.indexes
>> > where object_id>1000 -- eliminate some system table junk below 1000
>> > group by object_id
>> > order by TableName
>> >
>> >
>>
>

Friday, March 30, 2012

How to script index in 2005

How to script index in 2005

How to schedule DBCC INDEXDEFRAG the best way

Monday, March 12, 2012

How to return all tables that have an index?

How to return all tables that have an index?

How to return all tables that have an index?

restrict access to database

Blog Archive

About Me