Solved

Generate script to create all Indexes, keys, defaults

Posted on 2011-03-15
1
676 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:Auerelio Vasquez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

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 …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 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