How to test if Index exists?

How can I test if a table already has a specific index present?
Id normally test for an object with: if object_ID('My Object') is null then...
What technique works with an index? Or can you use the object_Id function, how do you reference the index?
Many thanks.You can check if a table has an index at all, not a specific index, unless you want to test for an existance of the name:

if exists (select 1 from dbo.sysindexes where object_name(id)='your_table' and indid between 2 and 254) print 'There is at least 1 non-clustered index'
else print 'No non-clustered indexes found'|||IF INDEXPROPERTY ( OBJECT_ID('your_table') , 'your_table_index' , 'IndexID' ) IS NULL

If you know the name the above should work; I have not tested it.

Tim S|||sp_helpindex tableName

OR

IF EXISTS (SELECT indid
FROM sysindexes
WHERE id = OBJECT_ID('tableName')
AND name = 'indexName') THEN

....sql

How to test if a full backup exists

I'm writing a automated backup routine for both SQL Server 2000 and 2005 that
will be a combination of full (once a week) and differential (all other days
of the week) backups.
My problem is that if someone creates a new database I need to automatically
take a full backup before the differential backup (or I'll get error 3035
"Cannot perform a differential backup"). So I would like to know if there is
a way to test if a full backup has been taken for a specific database?
Yes, I know about msdb.dbo.backupset but that's not a fullproof solution
since someone can create a new database with the same name as a previously
backed up database.
I have searched through DATABASEPROPERTY, DATABASEPROPERTYEX and all columns
in sysdatabases, sysfiles etc without any luck. Have I missed something or is
this property not available?
-AllanYou can use a combination of the name, create date, type and backup finish
date between the sysdatabases and backupset tables to determine whether a
full backup has occured for a given database. create date is found on both
backupset and sysdatabases and is the key to making sure the backup is
associated with the correct "version" of databases created over time with
the same name. NOTE: you will need to lop off the milliseconds to get the
join to work
sysdatabases.crdate
2007-05-01 09:35:14.553
backupset.database_creation_date
2007-05-01 09:35:14.000
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Allan" <allan@.newsgroups.nospam> wrote in message
news:C3484A5E-287C-4953-98DF-C34762A684CD@.microsoft.com...
> I'm writing a automated backup routine for both SQL Server 2000 and 2005
> that
> will be a combination of full (once a week) and differential (all other
> days
> of the week) backups.
> My problem is that if someone creates a new database I need to
> automatically
> take a full backup before the differential backup (or I'll get error 3035
> "Cannot perform a differential backup"). So I would like to know if there
> is
> a way to test if a full backup has been taken for a specific database?
> Yes, I know about msdb.dbo.backupset but that's not a fullproof solution
> since someone can create a new database with the same name as a previously
> backed up database.
> I have searched through DATABASEPROPERTY, DATABASEPROPERTYEX and all
> columns
> in sysdatabases, sysfiles etc without any luck. Have I missed something or
> is
> this property not available?
> -Allan

How to suft through MSDB folders?

Hi everyone,

Primary platform is Framework 2.0.

My target now is to know programatically how many folder exists under MSDB folder as well as its names.

How to accomplish this?

Thanks in advance and regards,

this link might help : http://msdn2.microsoft.com/en-us/sql/ms403343.aspx#exists

Frank

|||Thanks a lot for that. I haven't any issue at all.

How to stop a script running

Is there any way of stopping a script running?
In my script if a test is true(eg a certain table doesn't exists), i want to stop any further execution of the script.Assuming that you have already looked at IF...ELSE and it does not work for you, you might consider WHILE.

Look in SQL BOL under "control of flow"

Regards,

hmscott|||you can bracket the block of code in IF clause, say if table exists, then do this..

In stored proc, you can also use RETURN to exit out of the code.|||Originally posted by jagnini
Is there any way of stopping a script running?
In my script if a test is true(eg a certain table doesn't exists), i want to stop any further execution of the script.

The RETURN statment will end a script|||Actually, a return will just send you to the next batch terminator (go). Try out the script below in a pubs database:

select *
from authors

return

select *
from titleauthor

go

select *
from titles|||Thats the situation we have; we have several sections with "go"s.

It would be a bit of a pain to put a test in at the beginning of each section.|||Check out GOTO, but you'll have to get rid of all your "GO"'s, except for the last one if you want to keep it. It just has to be after all labels that the GOTO can go to ;)|||Can you get rid of the GOs and use nested transactions?

BEGIN TRANSACTION TRAN_WRAPPER

BEGIN TRANSACTION TRAN1

IF @.@.ERROR<>0
BEGIN
ROLLBACK TRANSACTION TRAN_WRAPPER
END
COMMIT TRANSACTION TRAN1

BEGIN TRANSACTION TRAN2

IF @.@.ERROR<>0
BEGIN
ROLLBACK TRANSACTION TRAN_WRAPPER
END
COMMIT TRANSACTION TRAN2

COMMIT TRANSACTION TRAN_WRAPPER

This would roll back the entire process. Just a thought. I'm not sure what you're trying to do between the batches. :)