Post reply

Quickest Way to Find an Index

  • September 19, 2012 at 8:31 am

    #1539600

    Like this:

    declare @idxname sysname;

    set @idxname = 'idx_DatePosted';

    I am finding that I am writing more code using SQL Server 2008 syntax and forget to change it for SQL Server 2005.

  • September 19, 2012 at 8:45 am

    #1539606

    ok got a little farther

    no idea what this means:

    declare @idxname sysname;

    set @idxname = 'idx_DatePosted';

    declare @idxname sysname;

    set @idxname = 'idx_DatePosted';

    declare @SQLCmd varchar(max);

    select @SQLCmd = stuff((

    select 'union all' + char(13) + char(10) + 'select ''' + db.name + ''' as DatabaseName, object_name(idx.object_id) as TableName, idx.name as IndexName from ' + db.name +'.sys.indexes idx where idx.object_id in (select object_id from ' + db.name + '.sys.tables) and idx.name = ''' + @idxname +''' ' + char(13) + char(10)

    from sys.databases db

    where db.name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')

    for xml path(''),type).value('.','varchar(max)'),1,11,'');

    print @SQLCmd;

    exec(@SQLCmd);

    go

    Msg 1934, Level 16, State 1, Server NSAB-SS80-SQL-N, Line 6

    SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.

    Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or

    query notifications and/or xml data type methods.

  • September 19, 2012 at 8:47 am

    #1539608

    I am on 2005.

    Will this not work then ?

    Jim

  • September 19, 2012 at 9:08 am

    #1539622

    JC-3113 (9/19/2012)

    I am on 2005.

    Will this not work then ?

    Jim

    Yes, it will work on SQL Server 2005. You need to check the settings for QUOTED_IDENTIFIER on your server.

    Just verified after firing up SQL Server 2005 on my laptop.

  • September 26, 2012 at 10:52 am

    #1542545

    Thanks Lynn

    Jim

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply