Solved

Generate script to create all Indexes, keys, defaults

Posted on 2011-03-15
1
671 Views
Last Modified: 2012-05-11
Hi,

I would like to create a script that will generate for all non-clustered indexes, unique indexes in a database. I know with generate script you can do for all tables, but i just want these individually.
0
Comment
Question by:basile
1 Comment
 
LVL 4

Accepted Solution

by:
qasim_md earned 500 total points
ID: 35140574
Try this and let me know if it was helpful.Please change the collation name in this SQL, as per your DB collation...

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 = 'SQL_Latin1_General_CP1_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
 
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now