为数据库中的所有表创建索引？
我知道我可以用下面的命令在表格中索引一列：
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
但是，我有一个包含250个模式的数据库，每个模式有10个表。 我怎样才能为每个表检查列是否存在，然后为它创建索引（如果存在）？
我正在使用SQL Server 2012。
Banana答案的一个小变化是使用INFORMATION_SCHEMA.COLUMNS直接获取最终的表格列表：
-- 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
实现您想要的一种简单方法是通过information_schema.tables遍历所有表，然后在该表存在行的情况下创建索引：
-- 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