my little world

Donnerstag, 5. Januar 2012

Get Tables without any unique key column(s)

As i posted a SQL Server 2000 compatible version of this task in May (link), so I want to add today a version, that is using the new system tables of SQL Server 2005+.

USE YourDataBase;
GO

SELECT
ss.name SchemaName
,so.name TableName
FROM sys.objects so
INNER JOIN sys.schemas ss ON
    so.schema_id = ss.schema_id
WHERE
    so.type = 'U' /* only select user tables */
AND NOT EXISTS (
            SELECT 1
            FROM sys.indexes si
            WHERE si.object_id = so.object_id AND type_desc = 'CLUSTERED'

            UNION

            SELECT 1
            FROM sys.indexes si
            WHERE si.object_id = so.object_id AND (is_unique_constraint = 1 OR is_unique = 1)
            )

;

Sonntag, 22. Mai 2011

Get tables without primary key

Sometimes it is useful to get all tables which has no primary key set.

With this short script you can get tables with a missing primary key even on SQL Server 2000.

USE myDatabase 
GO 
SELECT 
so.NAME 
,so.CRDATE 
FROM sysobjects so 
WHERE 
    so.xtype = 'U' 
AND so.NAME NOT IN ( 
    SELECT 
    so.NAME 
    FROM sysobjects so 
    inner join sysindexes si ON 
        so.ID = si.ID 
    WHERE 
        so.xtype = 'U' 
    AND si.indid = 1 
    ) 
ORDER BY NAME

Samstag, 7. August 2010

Compress all tables of a database

For me, it is useful, to enable compression for al tables in a database.

EXEC sp_MSforeachtable
     @command1="PRINT 'Starting compressing ' + CONVERT(VARCHAR(MAX), GETDATE(), 120) + ' ?'"
    ,@command2="PRINT 'Compressing table....' ALTER TABLE ? REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)"
    ,@command3="PRINT 'Finished compressing ' + CONVERT(VARCHAR(MAX), GETDATE(), 120) + ' ?'"