I am of the opinion that every table should have a clustered index.  Also, I recently saw somewhere ( maybe SqlTeam, I don't remember ) a quote from Joe Celko, "You don't have a table unless you have a key."  Which totally sounds like something he would say.  BTW, I got to meet him @ Pass one year.  Dude is wicked smart.  Wicked.

Done with the name dropping.

====================================
Basic idea:
You have a poorly performing database.  You can't / don't have time to re-write all of the stored procedures or many of them or whatever.  Code can't change right now.  Run this script, find the tables without Clustered indexes and primary keys and start planning.

Clustered indexes allow SQL Server to work more efficiently.  Primary keys support the use of foreign keys.  Data integrity is sooo sexy.   Do say this to your boss.  They will love it and promote you.

Things to keep in mind:

• Creating / changing a clustered index on a table means that EVERY row in the table must be moved.  Bad news: a BIG table will take a LONG time.  Good news:  a BIG table will show a HUGEr performance improvement.
• read up on clustered indexes and primary keys before beginning.  While they are often set on the same column or set of columns, they are not the same thing.
• Primary keys by definition are on unique columns.  Consider this:  If you aren't sure, I mean 100% positive, that the columns you have chosen for your primary key will be unique, you are probably going to break something.  If there is data in the table, then the creation of the key will not be successful.  But perhaps you are placing the key on a table which at the moment is empty.  Then the application which has been rolling along for the last 2.5 years goes cablooey trying to insert.  Be careful.  ( Yet another good example of why designing first, then coding saves lives ).

USE master

go

IF NOT EXISTS ( SELECT TOP 1
                    1
                FROM
                    sys.databases
                WHERE
                    name = 'MaasSqlTest' ) 
    BEGIN
        CREATE DATABASE MaasSqlTest
    END

GO

USE MaasSqlTest

GO

IF ( OBJECT_ID('dbo.TablesWithoutPKorCL') IS NULL ) 
    BEGIN
        --YEUP, if the script is successful, this table WILL show up in itself
        create TABLE [dbo].[TablesWithoutPKorCL]
            (
              [DBName] [nvarchar](128) NULL
            , [SchemaName] [sysname] NOT NULL
            , [TableName] [sysname] NOT NULL
            , [HasUniqueCnst] [int] NULL
            , [HasClustIndex] [int] NULL
            , [HasPrimaryKey] [int] NULL
            , IFixedThis BIT DEFAULT(0)
            , ImNotAllowedToFixThis BIT DEFAULT(0)
            , ThisCantBeFixedNow BIT DEFAULT(0)
            , UTCDateEntered DATETIME DEFAULT ( GETUTCDATE() )
            )
        ON  [PRIMARY]

    END
GO


GO

DECLARE @SQL NVARCHAR(MAX) ;

SET @SQL = '
INSERT INTO [MaasSqlTest].[dbo].[TablesWithoutPKorCL]
           (
            [DBName]
           ,[SchemaName]
           ,[TableName]
           ,[HasUniqueCnst]
           ,[HasClustIndex]
           ,[HasPrimaryKey]
            )
select
DB_Name() DBName
, S.name SchemaName
, T.name TableName
, OBJECTPROPERTY( object_id, ''TableHasUniqueCnst'') HasUniqueCnst
, OBJECTPROPERTY( object_id, ''TableHasClustIndex'') HasClustIndex
, OBJECTPROPERTY( object_id, ''TableHasPrimaryKey'') HasPrimaryKey
from sys.tables T
JOIN sys.schemas S
ON T.schema_id = S.schema_id
WHERE OBJECTPROPERTY( object_id, ''TableHasPrimaryKey'') = 0
OR OBJECTPROPERTY( object_id, ''TableHasClustIndex'') = 0

'
print @SQL
DECLARE @ExecuteWhere NVARCHAR(MAX) ;
SET @ExecuteWhere = '{@DBName}.dbo.sp_executeSQL @stmt = N''{@SQL}''' ;
SET @SQL = REPLACE(@SQL , '''' , '''''') ;
SET @ExecuteWhere = REPLACE(@ExecuteWhere , '{@SQL}' , @SQL) ;

DECLARE @dbs TABLE ( DBName sysname )
INSERT INTO
    @dbs ( DBName )
    select
        name
    from
        sys.databases
    where
        name not like 'Staging%'
DECLARE @DBName sysname ;
DECLARE @ExecuteWhere_DBName NVARCHAR(MAX) ;
while exists ( select top 1
                1
               from
                @dbs )
    BEGIN
        SELECT TOP 1
            @DBName = DBName
        FROM
            @dbs
        SET @ExecuteWhere_DBName = REPLACE(@ExecuteWhere , '{@DBName}' , @DBName) ;

        PRINT @ExecuteWhere_DBName ;

        EXECUTE ( @ExecuteWhere_DBName ) ;

        DELETE
            @dbs
        WHERE
            DBName = @DBName
    END

select *
from [MaasSqlTest].[dbo].[TablesWithoutPKorCL]