check if tables has same columns I have a Database that creates day tables and some of the tables don't have the same number of columns and in some cases the columns aren't the same.  What I'm needing to do is consolidate all the tables into a single table.  I'm not quite sure how to do this, but I'm assuming I need to make sure all the tables have the same columns and then add all the data from the multiple tables to the single table should be fairly easy.

so my first step, find out which tables are different and which ones are the same.

what I need is to find out if a table has the same columns as another.  not the data, but rather like the column names and possibly the data type for that column.

any help would be greatly appreciated.  Thank you.  I'm manually going through these and checking the content, but would definitely like something that would be much quicker.



UPDATE

the table's have a date related naming scheme.  here's one for an example: repo_2012_08_04
A simple approach if all you care about is column names and merely identifying any tables that have the same naming scheme but have columns that don't exist in the "model" or "ideal" table (also, I kept this simple, assuming all tables are in dbo):

USE tempdb;
GO

CREATE TABLE dbo.repo_2012_08_04(a INT, b INT); -- model
CREATE TABLE dbo.repo_2012_08_05(a INT, b INT); -- same schema as model
CREATE TABLE dbo.repo_2012_08_06(a INT, c INT); -- different
CREATE TABLE dbo.repo_2012_08_07(a INT, b INT, c INT); -- different
GO

DECLARE @model SYSNAME = N'repo_2012_08_04';

SELECT t.name FROM sys.tables AS t
WHERE name LIKE N'repo[_]%'
AND name &lt;&gt; @model
AND EXISTS
(
  SELECT 1 FROM sys.columns AS c 
    WHERE [object_id] = t.[object_id]
    AND NOT EXISTS
    (
      SELECT 1 FROM sys.columns
      WHERE [object_id] = OBJECT_ID(N'dbo.' + QUOTENAME(@model))
      AND name = c.name
    )
);
GO

DROP TABLE repo_2012_08_04, repo_2012_08_05, 
           repo_2012_08_06, repo_2012_08_07;
GO


Results:

repo_2012_08_06
repo_2012_08_07