SQL : Table Two-Way Column Comparison

SQL : Table Two-Way Column Comparison

This article was originally posted here: http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx I have found it useful so thought I would repost it here.

The following SQL will create a stored procedure that will perform a two-way comparison of columns in different tables.

CREATE PROCEDURE CompareTables(@table1 varchar(100),
 @table2 Varchar(100), @T1ColumnList varchar(1000),
 @T2ColumnList varchar(1000) = ”)
AS
 
— Table1, Table2 are the tables or views to compare.
— T1ColumnList is the list of columns to compare, from table1.
— Just list them comma-separated, like in a GROUP BY clause.
— If T2ColumnList is not specified, it is assumed to be the same
— as T1ColumnList.  Otherwise, list the columns of Table2 in
— the same order as the columns in table1 that you wish to compare.

— The result is all rows from either table that do NOT match
— the other table in all columns specified, along with which table that
— row is from.

 
declare @SQL varchar(8000);
IF @t2ColumnList = ” SET @T2ColumnList = @T1ColumnList
set @SQL = ‘SELECT ”’ + @table1 + ”’ AS TableName, ‘ + @t1ColumnList +
 ‘ FROM ‘ + @Table1 + ‘ UNION ALL SELECT ”’ + @table2 + ”’ As TableName, ‘ +
 @t2ColumnList + ‘ FROM ‘ + @Table2
set @SQL = ‘SELECT Max(TableName) as TableName, ‘ + @t1ColumnList +
 ‘ FROM (‘ + @SQL + ‘) A GROUP BY ‘ + @t1ColumnList +
 ‘ HAVING COUNT(*) = 1’
exec ( @SQL)

You can execute the query using the command:

DECLARE    @return_value int
EXEC    @return_value = [dbo].[CompareTables]
        @table1 = N’otProductSiteInclusion’,
        @table2 = N’otDealers’,
        @T1ColumnList = N’dealerid’,
        @T2ColumnList = N’dealerid’
SELECT    ‘Return Value’ = @return_value
GO

SQL : Table Column Comparison

SQL : Table Column Comparison

The following SQL will perform a one way comparison of a column from one table with another. Any listed items do not exist in the second table:

— Identify if values in Table1 exist in Table2
Select DealerID
FROM Table1
WHERE NOT EXISTS
(SELECT 1 FROM Table2
WHERE Table1.DealerID = Table2.DealerID

This can be extended to multiple columns:

Select DealerID, Name
FROM TABLE1
WHERE NOT EXISTS
(SELECT 1 FROM TABLE2
WHERE Table1.DealerID = Table2.DealerID
AND Table1.Name = Table2.Name
)

SQL : View Table Index Data

SQL : View Table Indexes

The following SQL will show index related information for a specific table within the local database context, this data will contain the index name and indexed fields:

EXEC sp_helpindex ‘messages’

You can view index fragmentation data on a per table basis using the command:

DBCC SHOWCONTIG tablename

For a more easy-to-read fragmentation report using the following query which will display indexes fragmented more than 40%, if you wish to modify the display criteria change the line highlighted in red (WHERE LogicalFrag > 40):

SET NOCOUNT ON

print ‘Cleaning up old temp tables’
IF object_id(‘tempdb..#spt_space’) IS NOT NULL
    BEGIN
       DROP TABLE #spt_space
    END

IF object_id(‘tempdb..#bigtables’) IS NOT NULL
    BEGIN
       DROP TABLE #bigtables
    END

IF object_id(‘tempdb..#fraglist’) IS NOT NULL
    BEGIN
       DROP TABLE #fraglist
    END
    
declare @tablename varchar (128)
declare @id int
declare @Cnt smallint
declare @nrc_mask int
declare @dbname varchar (200)

CREATE TABLE #spt_space
    (    aidi    int,
        [rows]        int null,
        reserved    dec(15) null,
        AUTOSTATS      char(2) null,
        [Last Updated] datetime
    )
    
CREATE TABLE #fraglist (
   ObjectName CHAR (255), ObjectId INT,
   IndexName CHAR (255), IndexId INT,
   Lvl INT, CountPages INT,
   CountRows INT, MinRecSize INT,
   MaxRecSize INT, AvgRecSize INT,
   ForRecCount INT, Extents INT,
   ExtentSwitches INT, AvgFreeBytes INT,
   AvgPageDensity INT, ScanDensity DECIMAL,
   BestCount INT, ActualCount INT,
   LogicalFrag DECIMAL, ExtentFrag DECIMAL
    )
    
CREATE TABLE #bigtables (
    i int NOT NULL IDENTITY(1,1),
    tablename varchar(128)
    )

/* Create cursor for per-table analysis and analyse tables for INDEX and AUTOSTATS data */
print ‘Creating cursor or per-table analysis’
declare tables cursor for
   select TABLE_SCHEMA + ‘.’ + TABLE_NAME
   from INFORMATION_SCHEMA.TABLES
   where TABLE_TYPE = ‘BASE TABLE’

print ‘Executing tables cursor’
open tables
    /*
    ** Set NORECOMPUTE mask
    */
    set @nrc_mask = 16777216

fetch next
   from tables
   into @tablename
while @@fetch_status = 0
begin

    select @id = object_id(@tablename)

    insert into #spt_space (aidi, reserved)
        select @id, sum(reserved)
            from sysindexes
                where indid in (0, 1, 255)
                    and id = @id
    update #spt_space set     [rows] = i.[rows]
            from #spt_space inner join sysindexes i
            on #spt_space.aidi = i.id
                where i.indid < 2
                    and i.id = @id
    update #spt_space set     [AUTOSTATS] =
                case (si.status & @nrc_mask)
                when @nrc_mask then ‘OFF’
                else ‘ON’
            end,
               [Last Updated] = stats_date(#spt_space.aidi, si.indid)
        from sysindexes si inner join #spt_space
            on #spt_space.aidi = si.id
        where si.id = @id            AND        — Table
              si.indid BETWEEN 1 AND 254            — Skip HEAP/TEXT index
   fetch next
      from tables
      into @tablename
end

print ‘Closing and deallocating tables cursor’
close tables
deallocate tables
    
INSERT #bigtables(tablename)
SELECT object_name(aidi) as tablename
FROM #spt_space
WHERE rows >= 100000

SELECT @Cnt = count(*) FROM #bigtables

DECLARE @DTime datetime
Set @DTime = GETDATE()

/* Obtain INDEX fragmentation data */
print ‘Updating Fragmentation temp table’
SET @id = 1
WHILE @id <= @Cnt
BEGIN
   SELECT @tablename = tablename FROM #bigtables WHERE i = @id
   INSERT INTO #fraglist
   EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’)
    WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’)     
   SET @id = @id + 1
END

/* Display Fragmentation Data > 40% */
SELECT ObjectName, ObjectId, IndexName, IndexId, Lvl, CountPages,
           ExtentSwitches, ScanDensity, BestCount , ActualCount ,LogicalFrag  
FROM #fraglist
WHERE LogicalFrag > 40
ORDER BY LogicalFrag DESC
       
DROP TABLE #spt_space
DROP Table #bigtables
DROP TABLE #fraglist

SQL : View Table Size

SQL : View Table Size

The following SQL will provide size infomation (in KB) for each table in the local database contex in a single table:

  SET NOCOUNT ON
  DBCC UPDATEUSAGE(0)

  SELECT sysobjects.[name] AS [TableName],
    SUM(sysindexes.reserved) * 8 AS [Size(KB)],
    SUM(sysindexes.dpages) * 8 AS [Data(KB)],
    (SUM(sysindexes.used) – SUM(sysindexes.dpages)) * 8 AS [Indexes(KB)],
    (SUM(sysindexes.reserved) – SUM(sysindexes.dpages)) * 8 AS [Unused(KB)]
  FROM dbo.sysindexes AS sysindexes
    JOIN dbo.sysobjects AS sysobjects ON sysobjects.id = sysindexes.id
  WHERE sysobjects.[type] = ‘U’
  GROUP BY sysobjects.[name]
  ORDER BY [Size(KB)] DESC

 

 

SQL : View Table Row Count

SQL : View Table Row Count

The following SQL will provide table name and rowcount for every table in the local database context:
  SELECT object_name (i.id) TableName, rows as RowCnt
  FROM sysindexes i INNER JOIN sysObjects o ON (o.id = i.id AND o.xType = ‘U’)
  WHERE indid < 2
  ORDER BY RowCnt

The reults will be similar to the output below:

tableName RowCnt
 messages  6342
 orders  3221
 dealer  101