Solved

Generate script to create all Indexes, keys, defaults

Posted on 2011-03-15
1
672 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

813 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

18 Experts available now in Live!

Get 1:1 Help Now