Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I wrote some scripts for correcting my old sql 2000 db schema compared to new sql 2000 db. This is done through a loop operations. First check if any table not found in my old db, then running a table creation script for creating that table. If any system stored procedures or scripts availble for table creation ?. I want table scripts like when we take 'generate sql script' process.

share|improve this question

1 Answer 1

There are no Transact SQL system functions or procedures provided by Micrsoft that generate object creation statements for you. You might be able to find scripts that others have written and posted to the internet.

There are things that you can do with other languages like VB, VB.Net, C#, Powershell or even VBScript (through Windows Scripting Host). I do not think that Transact-SQL is a good language to use for tasks that require manipulation of large strings, file I/O or even connecting to many other SQL Servers (to do things like schema comparisons). PowerShell, VB.Net, C# or even VBScript are much more flexible and provide simple ways to write files and connect to another server (or a thousand other servers). Whenever I want to do anything "complicated" and I can use something else, I always use something else.

IIRC, you can the SQL-DMO objects to create scripts fairly easily. SQL-DMO is a set of objects that were installed with SQL Server 2000 tools. These objects provided easy programatic administration of SQL Server 2000 and 7.0 (and maybe even older versions like 6.5).

The problem I am seeing right now is that SQL 2000 is very old (SQL-DMO was superceded by SMO in SQL Server 2005) and I am not having much luck finding a programming reference for the SQL-DMO library through google. (Perhaps someone else has a link they could share?)

SMO is another set of objects that can do anything that SQL-DMO could do. SMO is installed with the SQL Server 2005 (and later) tools. IIRC, it is also available as a downloadable installer off of Microsoft's site.

SMO provides a fairly easy to use .Script() function for all database objects, so it is largely a process of finding the missing object, get a reference to the existing, "good" object, setting appropriate flags, calling .Script() and putting the results in a file for later use. Picking the appropriate flags is the hardest part. (Flags affect the behavior of .Script(). For example, you can generate a script with or without primary keys, with or without relevenant indexes, etc. etc.)

TL;DR:

IMO: There are better ways to use your skills than writing programs that have already been written.

I'd suggest that you look at a tool like Redgate's "SQL Compare", Visual Studio 2010's own database comparison functions, ErWin, Dezign or OpenDBDiff (which is open source--I've heard positive things about but never actually used it) to build difference scripts. There are many other such tools. Nearly any database ERD/logical/physical relational database designer tool should be able to provide such scripts nowadays. These tools can do all sorts of useful things besides finding missing tables. They can find missing columns, missing indexes, different data types, different versions of stored procedures or functions, differences in permissions, etc.

You will want to make sure that the tool you choose is compatible with SQL 2000 before relying on it. For example, OpenDBDiff claims compatibility with SQL Server 2005/2008 and doesn't mention SQL Server 2000. So it might work OK or it might not.

If you really want to write your own tool, I'd look at using Powershell or C# to leverage the functionality in SMO. SMO has better features and is still well-supported, even in the "new era" of Powershell cmdlets.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.