NW Business Intelligence

thoughts on technology, B.I., and more…

Archive for January, 2009

Compare SQL Server tables

Posted by Brad Greene on January 5, 2009

Once in a while it’s nice to compare the list of tables in one database with another to see what might be missing. Here is a simple select statement to do that. In this case the databases are on different servers as well. The server names are in brackets. Any table missing in database B will show as ‘NULL’ in the list next to the table name found in database A.

You need the ability to connect to both of servers in SQL Server Management Studio. I’m sure there are other ways to do this depending on what level of access you have or tool you are using. This works in SQL Server 2005 at least.

select A.TABLE_NAME, B.TABLE_NAME from [DB_A].database_A.INFORMATION_SCHEMA.TABLES A
left join [DB_B].database_B.INFORMATION_SCHEMA.TABLES B
on A.TABLE_NAME = B.TABLE_NAME
order by 1

You can refine it by limiting it to only those that are missing in B this way:

select A.TABLE_NAME, B.TABLE_NAME from [DB_A].database_A.INFORMATION_SCHEMA.TABLES A
left join [DB_B].database_B.INFORMATION_SCHEMA.TABLES B
on A.TABLE_NAME = B.TABLE_NAME
where
B.TABLE_NAME is null
order by 1

Posted in Tech Tips | Comments Off on Compare SQL Server tables

Calculating next week

Posted by Brad Greene on January 1, 2009

Got this nice tip on how to calculate the date range for “next week” from a posting on IT Toolbox.

 Relies on the value of day number of the week in relation to the number of days in a week.

Here is the pseudo code: Some_Date minus Day_Number_in_Week + 8 gets the first day of the next week. If your weeks start with Sunday being day 1 then it would be Sunday. The last day of the week is derived the same way but by adding 14 instead of 8. Nice tip! Here is the link to the original posting.

Posted in Tech Tips | Comments Off on Calculating next week