Find Unsed Indexes

SELECT o.name,

       indexname=i.name,

       i.index_id,

       reads=user_seeks + user_scans + user_lookups,

       writes = user_updates,

       ROWS = (SELECT SUM(p.ROWS)

               FROM   sys.partitions p

               WHERE  p.index_id = s.index_id

                      AND s.object_id = p.object_id),

       CASE

         WHEN s.user_updates < 1 THEN 100

         ELSE 1.00 * ( s.user_seeks + s.user_scans + s.user_lookups ) /

              s.user_updates

       END                        AS reads_per_write,

       'DROP INDEX ' + Quotename(i.name) + ' ON ' + Quotename(c.name) + '.' +

       Quotename

       (Object_name(s.object_id)) AS 'drop statement'

FROM   sys.dm_db_index_usage_stats s

       INNER JOIN sys.indexes i

         ON i.index_id = s.index_id

            AND s.object_id = i.object_id

       INNER JOIN sys.objects o

         ON s.object_id = o.object_id

       INNER JOIN sys.schemas c

         ON o.schema_id = c.schema_id

WHERE  Objectproperty(s.object_id, 'IsUserTable') = 1

       AND s.database_id = Db_id()

       AND i.type_desc = 'nonclustered'

       AND i.is_primary_key = 0

       AND i.is_unique_constraint = 0

       AND (SELECT SUM(p.ROWS)

            FROM   sys.partitions p

            WHERE  p.index_id = s.index_id

                   AND s.object_id = p.object_id) > 10000

ORDER  BY reads 

Find Tables Without Primary Keys

SELECT Schema_name(o.schema_id) AS [schema],

       Object_name(i.object_id) AS [table],

       p.ROWS,

       user_seeks,

       user_scans,

       user_lookups,

       user_updates,

       last_user_seek,

       last_user_scan,

       last_user_lookup

FROM   sys.indexes i

       INNER JOIN sys.objects o

         ON i.object_id = o.object_id

       INNER JOIN sys.partitions p

         ON i.object_id = p.object_id

            AND i.index_id = p.index_id

       LEFT OUTER JOIN sys.dm_db_index_usage_stats ius

         ON i.object_id = ius.object_id

            AND i.index_id = ius.index_id

WHERE  i.type_desc = 'HEAP'

ORDER  BY ROWS DESC

Generate INSERT statments for tables

Generate Insert Statements for tables, a very handy script you will like to use.

 
CREATE PROCEDURE proc_CreateInsertForTable
  @tableName nvarchar(1000),
  @withDescription bit = 1
AS 
/*************************************************************************** 
  Written by Jennifer McCown, 9/13/2009 
  Jen@JenniferMcCown.com 
  http://www.MidnightDBA.com 
  Not copywrited; use, modify, and distribute freely! 
  
  Description: Generate an insert statement for a given table.  
  @withDescription controls the appearence of the "VALUES" portion of the 
  generated input statement.
   
  Please note that the insert statement will not include Identity columns. 

  EXEC exec proc_CreateInsertForTable @tableName = 'Video', @withDescription = 1
****************************************************************************/

SET NOCOUNT ON

DECLARE @sql1 varchar(8000), 
    @sql2 varchar(4000),
    @name varchar(500), 
    @type varchar(100)
    
DECLARE @mytable table (column_id int, 
        [name] nvarchar(500), 
        system_type_id int, 
        max_length int, 
        [precision] int, 
        scale int, 
        is_nullable bit, 
        is_identity bit, 
        readableType  varchar(100))

INSERT INTO @myTable
SELECT column_id, 
    c.[name], 
    c.system_type_id, 
    c.max_length, 
    c.[precision], 
    c.scale, 
    c.is_nullable, 
    c.is_identity, 
    t.[name] readableType
from sys.columns c
JOIN sys.types t on c.system_type_id = t.system_type_id
where object_name(c.object_id) = 'Video' order by c.column_id

-- Update ReadableType columns
UPDATE @myTable SET readableType = readableType 
    + ' (' + CAST(max_length as nvarchar(10)) + ')'
WHERE readableType IN ('char', 'varchar', 'nchar', 'nvarchar')

UPDATE @myTable SET readableType = readableType 
    + ' (' + CAST([precision] as nvarchar(10))  
    + ', ' + CAST([scale] as nvarchar(10))  + ')'
WHERE readableType IN ('decimal','numeric')

UPDATE @myTable SET readableType = readableType 
    + ' (' + CAST([precision] as nvarchar(10))  + ')'
WHERE readableType = 'float'


-- Seed the SQL variables with INSERT syntax
SET @sql1 = 'INSERT INTO ' + @tableName + ' (' 
SET @sql2 = ') ' + 'VALUES (' 


-- Open a cursor to add all the columns to INSERT statement
DECLARE insertCrs CURSOR FOR
SELECT [name], readableType from @myTable WHERE is_identity = 0

OPEN insertCrs 

-- Get first column
FETCH NEXT FROM insertCrs INTO @name, @type
SET @sql1 = @sql1 + @name
IF @withDescription = 1 
  SET @sql2 = @sql2 + ''''' /** ' + @name + ' ' + @type + ' **/ '
ELSE 
  SET @sql2 = @sql2 + ''''' '

-- Add additional columns
FETCH NEXT FROM insertCrs INTO @name, @type

  IF @withDescription = 1 
    WHILE @@FETCH_STATUS = 0
    BEGIN
      SET @sql1 = @sql1 + ', ' + @name
      SET @sql2 = @sql2 + ', '''' /** ' + @name + '**/ '
      FETCH NEXT FROM insertCrs INTO @name, @type
    END
  
  ELSE

    WHILE @@FETCH_STATUS = 0
    BEGIN
      SET @sql1 = @sql1 + ', ' + @name
      SET @sql2 = @sql2 + ', '''' '
      FETCH NEXT FROM insertCrs INTO @name, @type
    END


CLOSE insertCrs
DEALLOCATE insertCrs

SET @sql2 = @sql2 + ')'

SELECT @sql1, @sql2

SET NOCOUNT OFF
RETURN 0