Generate script of All the indexes in a database
This is an excellent article on SQL Server Central that I've used.
Here is another that you might like better.
If it's not exactly what you're looking for (sorting, filtering) I'm sure it would be easier to edit these than to start from scratch.
Here's the slightly corrected (2019) code from the first example:
-- Get all existing indexes, but NOT the primary keys
DECLARE cIX CURSOR FOR
SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID
FROM Sys.Indexes SI
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
WHERE TC.CONSTRAINT_NAME IS NULL
AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID
DECLARE @IxTable SYSNAME
DECLARE @IxTableID INT
DECLARE @IxName SYSNAME
DECLARE @IxID INT
-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @IXSQL NVARCHAR(4000) SET @IXSQL = ''
SET @IXSQL = 'CREATE '
-- Check if the index is unique
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
SET @IXSQL = @IXSQL + 'UNIQUE '
-- Check if the index is clustered
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
SET @IXSQL = @IXSQL + 'CLUSTERED '
SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('
-- Get all columns of the index
DECLARE cIxColumn CURSOR FOR
SELECT SC.Name
FROM Sys.Index_Columns IC
JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
ORDER BY IC.Index_Column_ID
DECLARE @IxColumn SYSNAME
DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1
-- Loop throug all columns of the index and append them to the CREATE statement
OPEN cIxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IxFirstColumn = 1)
SET @IxFirstColumn = 0
ELSE
SET @IXSQL = @IXSQL + ', '
SET @IXSQL = @IXSQL + @IxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn
END
CLOSE cIxColumn
DEALLOCATE cIxColumn
SET @IXSQL = @IXSQL + ')'
-- Print out the CREATE statement for the index
IF @IXSQL != '' BEGIN PRINT @IXSQL END
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
END
CLOSE cIX
DEALLOCATE cIX
Generate CREATE scripts for a list of indexes
You're pretty close, I'd say - I tried this, can you verify if this works for you and shows you the expected 122 indices to be recreated??
UPDATE: added functionality to determine CLUSTERED vs. NONCLUSTERED index type, and to add INCLUDEd columns to the index definition.
WITH indexCTE AS
(
SELECT DISTINCT
i.index_id, i.name, i.object_id
FROM
sys.indexes i
INNER JOIN
sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE
EXISTS (SELECT * FROM sys.columns c
WHERE c.collation_name = 'Modern_Spanish_CI_AS'
AND c.column_id = ic.column_id AND c.object_id = ic.object_id)
),
indexCTE2 AS
(
SELECT
indexCTE.name 'IndexName',
OBJECT_NAME(indexCTE.object_ID) 'TableName',
CASE indexCTE.index_id
WHEN 1 THEN 'CLUSTERED'
ELSE 'NONCLUSTERED'
END AS 'IndexType',
(SELECT DISTINCT c.name + ','
FROM
sys.columns c
INNER JOIN
sys.index_columns ic
ON c.object_id = ic.object_id AND ic.column_id = c.column_id AND ic.Is_Included_Column = 0
WHERE
indexCTE.OBJECT_ID = ic.object_id
AND indexCTE.index_id = ic.index_id
FOR XML PATH('')
) ixcols,
ISNULL(
(SELECT DISTINCT c.name + ','
FROM
sys.columns c
INNER JOIN
sys.index_columns ic
ON c.object_id = ic.object_id AND ic.column_id = c.column_id AND ic.Is_Included_Column = 1
WHERE
indexCTE.OBJECT_ID = ic.object_id
AND indexCTE.index_id = ic.index_id
FOR XML PATH('')
), '') includedcols
FROM
indexCTE
)
SELECT
'CREATE ' + IndexType + ' INDEX ' + IndexName + ' ON ' + TableName +
'(' + SUBSTRING(ixcols, 1, LEN(ixcols)-1) +
CASE LEN(includedcols)
WHEN 0 THEN ')'
ELSE ') INCLUDE (' + SUBSTRING(includedcols, 1, LEN(includedcols)-1) + ')'
END
FROM
indexCTE2
ORDER BY
TableName, IndexName
Do you get the CREATE INDEX
statements you're looking for??
Marc
How create only index script of all tables from database in MS SQL server?
SSMS scripting options are configurable. To include non-constraint indexes in the SSMS generated scripts, change the Script indexes option to true from the tools menu under Tools-->Options-->SQL Server Explorer-->Scripting-->.
In cases where SSMS scripting is insufficient for your needs, you can use SMO (e.g. Scripter via PowerShell or .NET code) or the cross-platform mssql-scripter command-line utility.
Generate CREATE INDEX statements in SQL Server
Use Generate Scripts from SQL Management Studio and choose the "Script Indexes" options (under Advanced Scripting options)
Generate script of all indexes, keys in the SQL Server database
Column names are stored in the sys.columns
andsys.index_columns
tables.
This will select the column names from all tables:
SELECT sc.name
FROM sys.tables st
INNER JOIN sys.columns sc ON sc.OBJECT_ID = st.OBJECT_ID
WHERE st.OBJECT_ID = 'ObjectID'
Or to join with the sys.indexes
table, this will select the table name and the columns:
SELECT st.name ,
sc.name
FROM sys.indexes si
INNER JOIN sys.tables st ON si.object_id = st.object_id
INNER JOIN sys.columns sc ON sc.OBJECT_ID = st.OBJECT_ID
You can add a WHERE
clause into the statement to filter on your index column
SELECT si.name ,
st.name ,
sc.name
FROM sys.indexes si
INNER JOIN sys.tables st ON si.object_id = st.object_id
INNER JOIN sys.columns sc ON sc.OBJECT_ID = st.OBJECT_ID
WHERE si.name = 'IX_EVT_EVENTS'
Is there a built-in tool to generate create table with index script in SQL Server 2012?
along with Table Structure want Indexes
Then
GO TO ->
SQLSERVER --> Tools --> Options
-->
SQL Server Object Explorer-->Scripting/Script Indexes
OR
GO TO -- > Generate Scripts --> It will give Table Script with Indexes
-- set to "True"
Auto generate script for CREATE TABLE including all indices, constraints, etc (not via SSMS)
Right-click on the database, select Tasks; there is Generate Scripts there. Just follow prompts or Google for additional information.
Related Topics
Sql Server Select Datetime Without Seconds
Does SQL Server Support Is Distinct from Clause
Cannot Connect to SQL Server Express from Ssms
Sqlite: Autoincrement Primary Key Questions
How to Analyze 'Dbcc Memorystatus' Result in SQL Server 2008
How to Insert into a Table with Just One Identity Column (Sql Express)
Importing and Validating Xml File Using Ssis or Just Plain T-Sql
Timestamp Conversion in Oracle for Yyyy-Mm-Dd Hh:Mm:Ss Format
Performance of Querying Across Two MySQL Databases on The Same Server
Delete ... from ... Where ... In
Does Sqlite Support Replication
Writing SQL Query for Getting Maximum Occurrence of a Value in a Column
Delphi: Accessing JSON Objects Within a JSON Array
Select The Last Row in a SQL Table
Hibernate 4.3.6 Querysyntaxexception: Path Expected for Join