Create indexes for all tables in database?

Question

I know I can index a column in a table with the command:

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

However, I have a database of 250 schemas with 10 tables each. How can I, for each table, check if a column exists, and then create an index for it (if it does exist)?

I am using SQL Server 2012.

score 6 · Accepted Answer · answered Aug 16 '14 at 16:32

A small variation on Banana's answer is to use INFORMATION_SCHEMA.COLUMNS to grab the final list of tables directly:

-- Define column to index.
DECLARE @coltoindex VARCHAR(20), @indexoptions VARCHAR(30)
SET @coltoindex = 'Id'
SET @indexoptions = 'UNIQUE'

--USE database_name
--IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables
SELECT table_schema, table_name INTO #tables FROM information_schema.columns
    where COLUMN_NAME = @coltoindex

DECLARE @schema VARCHAR(30), @table VARCHAR(20), @sqlCommand varchar(1000)
WHILE (SELECT COUNT(*) FROM #tables) > 0
BEGIN
    SELECT TOP 1 @schema = table_schema, @table = table_name FROM #tables
    SET @sqlCommand = '
            CREATE ' + @indexoptions + ' INDEX 
            idx_'  + @schema + '_' + @table + '_' + @coltoindex + '
            ON ' + @schema + '.' + @table + ' (' + @coltoindex + ')'
    -- print @sqlCommand
    EXEC (@sqlCommand)
    DELETE FROM #tables WHERE table_schema = @schema AND table_name = @table
END

I would suggest that you might add a ` IF (SELECT count(*) FROM sys.indexes WHERE name='idx_' + @schema + '_' + @table + '_' + @coltoindex) = 0` above the `SELECT TOP 1`, which stops it from dying if there's already an index on that table. — Maury Markowitz, Feb 16 '17 at 14:22

score 2 · Answer 2 · edited May 23 '17 at 12:09

A simple way of achieving what you want, is to loop over all tables through information_schema.tables and then create the index if the row exists in that table:

-- Define column to index.
DECLARE @coltoindex VARCHAR(20), @indexoptions VARCHAR(30)
SET @coltoindex = 'Id'
SET @indexoptions = 'UNIQUE'

USE database_name
--IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables
SELECT table_schema, table_name INTO #tables FROM information_schema.tables
DECLARE @schema VARCHAR(30), @table VARCHAR(20), @sqlCommand varchar(1000)
WHILE (SELECT COUNT(*) FROM #tables) > 0
BEGIN
    SELECT TOP 1 @schema = table_schema, @table = table_name FROM #tables
    SET @sqlCommand = '
        IF EXISTS(SELECT * FROM sys.columns  
        WHERE [name] = N''' + @coltoindex + ''' 
        AND [object_id] = OBJECT_ID(N''' + @schema + '.' + @table + ''')) 
        BEGIN 
            CREATE ' + @indexoptions + ' INDEX 
            idx_'  + @schema + '_' + @table + '_' + @coltoindex + '
            ON ' + @schema + '.' + @table + ' (' + @coltoindex + ')
        END'
    EXEC (@sqlCommand)
    DELETE FROM #tables WHERE table_schema = @schema AND table_name = @table
END

Seems odd that you'd use `INFORMATION_SCHEMA.TABLES` for the tables, but then `sys.columns` for the columns.... once the ANSI standard view, once the SQL Server specific one.... — marc_s, Aug 16 '14 at 16:32

Create indexes for all tables in database?

2 Answers2