June 3, 2010 at 6:23 am
Hi all,
I'm have been assigned to a new environment containing over 200 instances. On each instance is (besides application databases) a database located for administration purposes. I suspect some of these administrative databases are not up-to-date with the most recent version of table definitions and stored procedures.
Can anyone tell me what is the most easy way to compare database schemas between many instances?
I am familiar with Redgate SQL Compare, but that is only to compare a database between 2 instances. I like to have a compare of a database between 200 instances at one sweep with a report of differences for each instance.
Thanks in advance,
Hans.
June 3, 2010 at 7:26 am
I would still recommend using Red Gate SQL Compare. You can call the Pro version from a command line, passing parameters, and hit every single instance through a PowerShell script or a command line script, no problem. Output can be formatted from the command line as well. It'll do the job you need.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2010 at 3:36 am
Thanks Grant,
I will look into scripting Redgate SQL Compare. I was hoping someone knew a tool that has this function built-in. That would save me quite some work.
But this will give me a nice challenge and build my experience. :w00t:
June 4, 2010 at 4:04 am
Hi,
Another option could be to gather data from the INFORMATION_SCHEMA views on each SQL Server instance onto one server and then write a query to highlight any differences. Using SQLCMD/OSQL -L you can get a list of all SQL Servers on your network and a simple batch file such as the following could loop through the text file [SQLInstanceList.txt] (which contains the output from the SQLCMD/OSQL -L) and executes the .sql script [SchemaInformation.sql] which gathers the required information and writes it out to [AllInstanceSchemaReport_04062010.txt]
FOR /F "TOKENS=1,2*" %%A IN ('DATE/T') DO SET TODAY=%%A
FOR /F "DELIMS=/ TOKENS=1,2,3*" %%A IN ('ECHO %TODAY%') DO SET DAY=%%A
FOR /F "DELIMS=/ TOKENS=1,2,3*" %%A IN ('ECHO %TODAY%') DO SET MONTH=%%B
FOR /F "DELIMS=/ TOKENS=1,2,3*" %%A IN ('ECHO %TODAY%') DO SET YEAR=%%C
REM - Set the timestamp for the output file
set TIMESTAMP=%YEAR%%MONTH%%DAY%
FOR /F "TOKENS=1,2*" %%A IN ('TIME/T') DO SET RUNTIME=%%A
FOR /F "DELIMS=: TOKENS=1" %%A IN ('ECHO %RUNTIME%') DO SET HOUR=%%A
FOR /F "DELIMS=: TOKENS=2" %%B IN ('ECHO %RUNTIME%') DO SET MIN=%%B
Set DATE=%DAY%/%MONTH%/%YEAR% @ %HOUR%:%MIN%
REM prompt ::
REM Set the starting variables for the scripts
SET ENV=
SET LOC=C:\YourFolderLocation
set OUTPUT=%LOC%\AllInstanceSchemaReport_%TIMESTAMP%.txt
cd /D %LOC%
echo Start Job Owner check >%OUTPUT%
for /F "usebackq" %%i IN (`type SQLInstanceList.txt`) DO (
osql -S%%i -E -w100 -osql\SchemaInformation.sql >>%OUTPUT% -w1000 -n -b
)
It's a slightly "faffy" way of collecting the information but once you've got it setup you can use it to report/deploy against your entire estate in no time at all.
Hope that's of some help,
Chris
www.sqlAssociates.co.uk
June 4, 2010 at 6:20 am
i don't know if this will help you, as it's not a canned solution, just how I've done it in the past.
I created a simple vb6, and then improved it in a later .NET version.
The idea starts with adding a connection in my application to point to the "perfect"/model database that all other db's should be compared to, and creates an XML file of the table schemas(name, column definitions, defaults, etc etc) ,and all the procs / functions.
I have multiple XML files, one for each version/upgraded version of the database.
an end user version of the same application prompts the user to fill in the connection information for a database, and then compares the xml document(all downloadable from the web) to the currently selected database, producing both a report on the changes and the actual change script for any differences found. Sort of a homemade Red Gate Compare, i bet...though I've never seen the Red Gate product.
In our case, we can add a new xml doc to our web site any time we issue an upgrade, and the end users can confirm their db is not missing anything.
Here's a basic SQL that pulls a lot of the Column info together for comparison:
SELECT
schema_name(objectz.schema_id) as schemaname,
objectz.name AS tablename,
objectz.type_desc,
calc.definition as calculated_column_definition,
CASE
WHEN COLUMNPROPERTY ( objectz.object_id , columnz.[name] , 'IsIdentity' ) = 0
THEN ''
ELSE ' IDENTITY(' + CONVERT(VARCHAR,ISNULL(IDENT_SEED(objectz.object_id),1) )
+ ','
+ CONVERT(VARCHAR,ISNULL(IDENT_INCR(objectz.object_id),1) )
+ ')'
END AS identity_seed,
CASE
WHEN columnz.[default_object_id] = 0
THEN ''
ELSE ISNULL(def.[definition] ,'')
END AS default_definition,
CASE
WHEN columnz.[default_object_id] = 0
THEN ''
ELSE def.name
END as default_name,
TYPE_NAME(columnz.[system_type_id]) AS sys_type_name,
TYPE_NAME(columnz.[user_type_id]) AS user_type_name,
columnz.*
FROM sys.objects objectz
INNER JOIN sys.columns columnz
ON objectz.object_id = columnz.object_id
LEFT OUTER JOIN sys.computed_columns calc
ON columnz.object_id = calc.object_id
AND columnz.column_id = calc.column_id
LEFT OUTER JOIN sys.default_constraints def
ON columnz.[default_object_id] = def.[object_id]
--WHERE objectz.type_desc IN('USER_TABLE','VIEW')
WHERE objectz.type_desc IN('USER_TABLE')
AND objectz.is_ms_shipped = 0
ORDER BY columnz.object_id,columnz.column_id
Lowell
June 4, 2010 at 7:00 am
Thanks Lowel and Chris,
Your ideas point me to some new thoughts. I think it must be possible to transfer DDL from system tabels to a central location. By joining these data I can extract the differences.
I will work out some solutions and pick the one that works best for me.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply