SQL – Table comparison

For table comparison, i like to use Red Gate SQL compare. Great tool, but if you don’t have money for it, you can always try to write your own comparation script.
You can then script it as function, and use it. It does not have, options like SQL Compare, but for now it will do the trick.
This simple comparation, uses only names of columns to compare. But you can code it to check also field type and field length.

First of all, lets create two simple tables:

CREATE TABLE [dbo].[Table_One](
    [Column1] [nchar](10) NULL,
    [Column2] [varchar](50) NULL,
    [Column3] [int] NULL,
    [Coulmn4] [datetime] NULL,
    [Column10] [char](20) NULL
) ON [PRIMARY]

CREATE SCHEMA [test] AUTHORIZATION [dbo]
GO
CREATE TABLE [test].[Table_Two](
    [Column1] [nchar](10) NULL,
    [Column7] [int] NULL,
    [Column8] [int] NULL,
    [Coulmn9] [datetime] NULL,
    [Column10] [varchar](20) NULL
) ON [PRIMARY]

As you see, I left

Then, declare variables for table names. It will be easier to handle table names in one place.
DECLARE @T1 VARCHAR(30) = ‘Table_One’, –names without schemas
        @T2 VARCHAR(30) = ‘Table_Two’

 

Now, we just have to create comparison select. Selecting distinct values from information_schema view first for one table, than the other.

— In TableOne but not in TableTwo
SELECT DISTINCT
       @T1 AS [First table],
       ‘>>’ AS Dir, –Direction
       @T2 AS [Second table],
       ISC1.COLUMN_NAME,
       ISC1.DATA_TYPE        
  FROM INFORMATION_SCHEMA.COLUMNS ISC1
WHERE ISC1.COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS ISC2 WHERE ISC2.TABLE_NAME = @T2)
        AND ISC1.TABLE_NAME = @T1
UNION ALL
— In TableTwo but not in TableOne
SELECT DISTINCT
       @T1 AS [First_table],
       ‘<<‘ AS Dir, –Direction
       @T2 AS [Second_table],
       ISC1.COLUMN_NAME,
       ISC1.DATA_TYPE        
  FROM INFORMATION_SCHEMA.COLUMNS ISC1
WHERE ISC1.COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS ISC2 WHERE ISC2.TABLE_NAME = @T1)
        AND ISC1.TABLE_NAME = @T2
ORDER BY Dir DESC, COLUMN_NAME ASC

 

And the result is:
image

 

Good Luck

Simple Calendar–first thing in BI reporting

When you are building or creating rudimentary BI for someone, there are always some parameters you have to embed in your query. But, almost always there is date to date parameters. So, it is very helpful to have a calendar table, where you know which days are weekends, which days are working days, what week of the year it is, to which quarter does some date belong to, etc…

 

So first create table:

CREATE TABLE dbo.Calendar
(   date SMALLDATETIME NOT NULL  PRIMARY KEY CLUSTERED,
weekend bit,
year bit,
quartal bit,
month bit,
day bit,
day_of_week bit,
month_name varchar(20),
day_name varchar(20),
week_in_year bit
)

 

Now we have to fill the table. For this query, you must set your start date, maximal date, language for names and type of query to execute.
My start date is 01.01.2010, end date is 31.12.2050, language Croatian and I’ll work with recursive CTE query.

SET LANGUAGE CROATIAN – can use also SET LANGUAGE HRVATSKI

declare @begdate datetime,
@enddate datetime

set @begdate = ‘2010-01-01’
set @enddate = ‘2050-12-31’

;
with Ctecalendar(date,isweekend, y, q,m,d,dw,monthname,dayname,w) as –define CTE
(
select @begdate ,
case when datepart(dw,@begdate ) in (6,7) then 1 else 0 end, –saturday and sunday
year(@begdate ),
datepart(qq,@begdate ),
datepart(mm,@begdate ),
datepart(dd,@begdate ),
datepart(dw,@begdate ),
datename(month, @begdate ),
datename(dw, @begdate ),
datepart(wk, @begdate )
union all
select date + 1,
case when datepart(dw,date + 1) in (6,7) then 1 else 0 end,
year(date + 1),
datepart(qq,date + 1),
datepart(mm,date + 1),
datepart(dd,date + 1),
datepart(dw,date + 1),
datename(month, date + 1),
datename(dw, date + 1),
datepart(wk, date + 1) from Ctecalendar where date + 1<= @enddate
)
insert into dbo.Calendar
select date,isweekend, y, q,m,d,dw,monthname,dayname,w from Ctecalendar
option(maxrecursion 32767) –bad query can cause infinitive loop, so you set this to loop 32767 times, 0 for infinitive

So there you have it, simple Calendar table for your queries!