Scripts

Parent-Child Table Dependency Script

Aalam RangiLeave a comment

Requirement:

You need to delete and then insert data into tables which have parent-child relationships defined via primary and foreign keys. The delete and insert commands have to be executed in proper sequence.

The sequence for DELETE is Child-before-Parent.

The sequence for INSERT is Parent-before-Child.

I created a quick script in response to a forum post. The script is not the best out there because –

  • It does not handle circular references in relationships
  • It takes 30 seconds or more if run on a database with hundreds of tables

I’m sharing it for my future reference and probably others with smaller number of tables would find it useful too.

Download script at –
http://gallery.technet.microsoft.com/Parent-Child-Table-45fa5268

Further reference:
For a faster (but not with all these output columns) script, look at Erland Sommarskog’s response in the forum thread.

Script output would look like the example below –

TableDependencyReport

How to monitor IDENTITY column values in SQL Server

Aalam RangiLeave a comment

One (lesser known) task of a DBA should be to keep an eye on the values of the IDENTITY columns so that they do not hit the ceiling for their data type and catch you by surprise. Not a pleasant one, of course. This is all the more important if you have tables that grow quickly.

Vyas Kondreddi has a nice script to look for current IDENTITY values in all tables of a database and compare that value against its datatype. It could be useful to automate it as an alert or run it manually once in a while. He has versions of the script for SQL Server 7.0, 2000, 2005 and later but all of them run against one database at a time which might make it a little inconvenient to run against all your 200 databases.

I’ve taken the script compatible with SQL Server 2005 and later, and enhanced it a bit to run it against all databases in one go using the undocumented stored procedure SP_MSFOREACHDB. There goes your excuse to procrastinate this!

Change the value against the TOP keyword to suit your preference. I’ve used TOP 5.

Download my script from the TechNet Gallery link –
http://gallery.technet.microsoft.com/Monitor-IDENTITY-column-fd9c6552

The output would look like the screenshot below.

MonitorIdentityColumns

Oracle Sample Tables’ Script for SQL Server

Aalam Rangi4 Comments

I work primarily on SQL Server these days but I had originally started learning SQL on Oracle. I find the sample tables EMP, DEPT, BONUS, SALGRADE and DUMMY provided with Oracle to be useful for beginners to practice SQL commands. Oracle provides the script by the name of DEMOBLD.SQL to create these tables which can be found in the <OracleHome>sqlplusdemo folder of your Oracle installation or at this URL. Some more useful scripts for Oracle users can be found at this OraFAQ page.

I thought that few folks might like to have those tables on SQL Server so I made some changes to the script so that it can run on SQL Server too. It might prove useful if you don’t have access to an Oracle installation but want want to practice SQL commands with these tables. Of course, MS SQL Server does not support all the SQL syntax as supported by Oracle, but the tables can be handy in some cases.

The SQL Server compatible script is at the TechNet link below. The basic difference from the original script is in the datatypes and the datetime convert function. The script will drop any tables with the same names in the current database so be sure that existing tables do not have any critical data.

Download URL –
http://gallery.technet.microsoft.com/TSQL-Script-to-Create-6bc846bd