Posted by frien on 12/08/06 20:44

This is what i did
this gave no synatx error..............but is not doing what it is
suppose to do..
if i execute the query alone that is working fine....but am not able to
implement as a stored procedure


CREATE PROCEDURE [dbo].[sd_find_create_ind]
@tblName varchar(255),
@colName varchar(255),
@indName varchar(255)
AS
declare @query varchar(1024)
select @query = 'IF indexproperty(object_id('+@tblName+'),
'+@indName+', ''IsClustered'') IS NULL
CREATE INDEX '+@indName+' ON '+@tblName+'('+@colName+')'
GO

.......plz help me out
thnaks


Erland Sommarskog wrote:
> frien (001frien@gmail.com) writes:
> > can anybody plz....help in a stored procedure..............i basically
> > need a simple stored procedure to see if a index exists in the table if
> > it it doesnot exist i need to create a index for the same table
>
> IF indexproperty(object_id('mytable'), 'myindex', 'IsClustered') IS NULL
> CREATE INDEX myindex ON mytable(mycol)
>
> The key here is that indexproperty returns NULL is the index does
> not exist.
>
> This look-up is by name. A more ambitious check would look at the existing
> indexes, their columns and other properties.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]