Tuesday, January 4, 2011

tablediff command line utility in SQL Server

Above tablediff utility is very useful for comparing tables.It is not only tells us which records / columns are different but can also generate a SQL script to update the second table to make it the same as the first table.

You have to navigate to following path the use this utility

SELECT 'C:\Program Files\Microsoft SQL Server\100\COM>

Then type command ;

tablediff –?

Then it shows all the options as follows
 tablediff

But only limitation is you could compare one table at a time. Even though you can do some interesting work to overcome this. That is Just go to the SQL sever and type following command execute it. So it will generate the script for all the table. Then you can add it into .bat file.

SELECT 'C:\Program Files\Microsoft SQL Server\100\COM>tablediff -sourceserver "SourceDBName" -sourcedatabase "T1" -sourcetable ' + '"' + name +'" ' + '-destinationserver "DestinationDBName" -destinationdatabase "T2" -destinationtable ' + '"' + name +'" ' + ' -o d:\' + name +'_Script_out.txt'
FROM dbo.sysobjects
WHERE xtype = 'U'

Monday, January 3, 2011

SQLCMD command line utility

To run multiple external scripts do as follows;

Suppose you are having following scripts in a folder. c:\scripts\script1.sql, c:\scripts\script1.sql

If you add the following example to .bat file ,it runs those scripts from the command line and outputs the queries and results to a text file located at C:\Result.txt.

sqlcmd -S ERANGA-PC\SQLEXPRESS -d T1 -U sa -P Qwer1234 -i c:\scripts\script1.sql -i c:\scripts\script1.sql –o c:\scripts\Result.sql

Recurrent Work–Automate them

Problem

Sometimes we do some recurrent works those can be automated.

Few months back in a integration of two project I had to create multiple database scripts to merge those two. There are more than twenty scripts.Initially I run those one by one and it took long time to create the DB.

Mistake

We don’t care to automate those recurrent work .Just Do at the point you need. Once you get to know its recurrent you will regret I could have automate this.

Lesson Learned

By automating recurrent works [Those can automate] you can save your time

SQLCMD command line utility

Recently I work with SQLCMD utility and it is quite interesting. You can enable it in your Management studio as follows;

sqlcmd_1

Then you can make use color coding and syntax highlighting as well . Other than this ,you can enable this SQLCMD mode in your normal command line by setting path variable to binn folder as following example

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\

Then just open command line and then type this command.It will show the option available;

-sqlcmd –?

sqlcmd_2

To connect to a database and query for data

sqlcmd -S ERANGA-PC\SQLEXPRESS -d T1 -U sa -P Qwer1234 –q “Select * from Student”