The Ji Village News

季庄新闻–Haidong Ji’s Blog

Via Ranjith Kumar S, a script to drop all objects in a schema and then the schema itself in SQL Server. I made very slight modifications so stored procedure creation is unnecessary. All you need is adjusting values for @SchemaName and @WorkTest variables.

Limitations:
1. If a table has a PK with XML or a Spatial Index then it wont work
(workaround: drop that table manually and re run it)
2. If the schema is referred by a XML Schema collection then it wont work

Thanks Ranjith and enjoy!

declare @SchemaName varchar(100) = 'MySchema'
declare @WorkTest char(1) = 't'  -- use 'w' to work and 't' to print
declare @SQL varchar(4000)
declare @msg varchar(500)
IF OBJECT_ID('tempdb..#dropcode') IS NOT NULL DROP TABLE #dropcode
CREATE TABLE #dropcode
(
   ID int identity(1,1)
  ,SQLstatement varchar(1000)
 )
-- removes all the foreign keys that reference a PK in the target schema
 SELECT @SQL =
  'select
       '' ALTER TABLE ''+SCHEMA_NAME(fk.schema_id)+''.''+OBJECT_NAME(fk.parent_object_id)+'' DROP CONSTRAINT ''+ fk.name
  FROM sys.foreign_keys fk
  join sys.tables t on t.object_id = fk.referenced_object_id
  where t.schema_id = schema_id(''' + @SchemaName+''')
    and fk.schema_id <> t.schema_id
  order by fk.name desc'
 IF @WorkTest = 't' PRINT (@SQL )
 INSERT INTO #dropcode
 EXEC (@SQL)
 -- drop all default constraints, check constraints and Foreign Keys
 SELECT @SQL =
 'SELECT
       '' ALTER TABLE ''+schema_name(t.schema_id)+''.''+OBJECT_NAME(fk.parent_object_id)+'' DROP CONSTRAINT ''+ fk.[Name]
  FROM sys.objects fk
  join sys.tables t on t.object_id = fk.parent_object_id
  where t.schema_id = schema_id(''' + @SchemaName+''')
   and fk.type IN (''D'', ''C'', ''F'')'
 IF @WorkTest = 't' PRINT (@SQL )
 INSERT INTO #dropcode
 EXEC (@SQL)
 -- drop all other objects in order
 SELECT @SQL =
 'SELECT
      CASE WHEN SO.type=''PK'' THEN '' ALTER TABLE ''+SCHEMA_NAME(SO.schema_id)+''.''+OBJECT_NAME(SO.parent_object_id)+'' DROP CONSTRAINT ''+ SO.name
           WHEN SO.type=''U'' THEN '' DROP TABLE ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]
           WHEN SO.type=''V'' THEN '' DROP VIEW  ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]
           WHEN SO.type=''P'' THEN '' DROP PROCEDURE  ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]
           WHEN SO.type=''TR'' THEN ''  DROP TRIGGER  ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]
           WHEN SO.type  IN (''FN'', ''TF'',''IF'',''FS'',''FT'') THEN '' DROP FUNCTION  ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]
       END
FROM SYS.OBJECTS SO
WHERE SO.schema_id = schema_id('''+ @SchemaName +''')
  AND SO.type IN (''PK'', ''FN'', ''TF'', ''TR'', ''V'', ''U'', ''P'')
ORDER BY CASE WHEN type = ''PK'' THEN 1
              WHEN type in (''FN'', ''TF'', ''P'',''IF'',''FS'',''FT'') THEN 2
              WHEN type = ''TR'' THEN 3
              WHEN type = ''V'' THEN 4
              WHEN type = ''U'' THEN 5
            ELSE 6
          END'
IF @WorkTest = 't' PRINT (@SQL )
INSERT INTO #dropcode
EXEC (@SQL)
DECLARE @ID int, @statement varchar(1000)
DECLARE statement_cursor CURSOR
FOR SELECT SQLStatement
      FROM #dropcode
  ORDER BY ID ASC
 OPEN statement_cursor
 FETCH statement_cursor INTO @statement
 WHILE (@@FETCH_STATUS = 0)
 BEGIN
 IF @WorkTest = 't' PRINT (@statement)
 ELSE
  BEGIN
    PRINT (@statement)
    EXEC(@statement)
  END
 FETCH statement_cursor INTO @statement
END
CLOSE statement_cursor
DEALLOCATE statement_cursor
IF @WorkTest = 't' PRINT ('DROP SCHEMA '+@SchemaName)
ELSE
 BEGIN
   PRINT ('DROP SCHEMA '+@SchemaName)
   EXEC ('DROP SCHEMA '+@SchemaName)
 END
PRINT '------- ALL - DONE -------'

For SQL Server management from Linux, I’ve used pymssql on Linux before, but realized lately that it is hard to configure with the heterogeneous environment I am in. So I am giving pyodbc a try. The install on Windows is simple. Listed below is some code that I put together quickly for some testing. It lists all files used by a SQL Server instance. I will test pyodbc on Linux against a SQL Server database and will write my findings in a few days.

How to reproduce:
1. Install Python 2.7 on Windows. I am not bothering with 64-bit Python at the moment;
2. Download and install pyodbc 2.7 for Windows;
3. Add c:\Python27 to your PATH variable

""" The code below lists all files used by a SQL Server instance. Adjust instance and login credentials as necessary. ROWS means data file, LOG means log file """
import pyodbc
cn = pyodbc.connect('DRIVER={SQL Server};SERVER=MyInstance;DATABASE=master;Trusted_Connection=yes')
cursor = cn.cursor()
cursor.execute("select name from sys.databases")
rows = cursor.fetchall()
for row in rows:
	sql = ("select type_desc, physical_name from %s.sys.database_files" % row.name)
	cursor.execute(sql)
	l1rows = cursor.fetchall()
	print "Files for " + row.name + " :"
	for l1row in l1rows:
		print l1row.type_desc + " " + l1row.physical_name

I wrote about unattened install of SQL Server 2008 R2 here, where all parameters were entered on the command line directly.

Another way, perhaps a better way, of automation is to put all parameters in a configuration file. Here is a sample:

[SQLSERVER2008]
; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.
ACTION="Install"
; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, and Tools. The SQL feature will install the database engine, replication, and full-text. The Tools feature will install Management Tools, Books online, Business Intelligence Development Studio, and other shared components.
FEATURES=SQL,Tools
INSTANCENAME=MSSQLSERVER
SQLSVCACCOUNT="NT AUTHORITY\SYSTEM"
SQLSYSADMINACCOUNTS=".\Administrator"
AGTSVCACCOUNT="NT AUTHORITY\Network Service"
;INSTANCEDIR=c:\SQLServer

Here is how to use it:

setup.exe /ConfigurationFile=SQL2008R2InstallConfig.txt /QS /IACCEPTSQLSERVERLICENSETERMS

One thing is worth of mentioning: installer creates a directory called MSSQL10_50.MSSQLSERVER under your INSTANCEDIR for the default instance. For a named instance, say Instance1, it will be MSSQL10_50.Instance1. The installer adds the MSSQL10_50. prefix and it does not look like it can be helped.

I’ve written unattended / silent install before for SQL Server 2005. Unattended install is cool because you can automate things: it saves time and improves consistency.

Here is something to get you started. I used the command below to:

1. Install SQL Server 2008 R2 default instance, including Replication and FullText search;
2. Install client tools such as: SQL Server Management Studio, Business Intelligence Development Studio, sqlcmd, Configuration Manager, and such;
3. Install Books Online so help is available locally.

In other words, it sets things up quickly for a typical OLTP DBA so s/he can start getting dirty and productive with minimal delay and distraction.

Things to keep in mind:

1. I didn’t care much about startup account (SQLSVCACCOUNT and AGTSVCACCOUNT) stuff for my testing here. Modify that as necessary. You can also change the startup account after the install is done. So don’t hang up on that if you don’t know what the domain\userAccount should be. Let the learning process continue;
2. Features is a list of features seperated by comma only, no space in between;
3. /qs is nice if you want visual dialog windows popping up telling you where you are at. It is neat to have that visual confirmation during the first few runs. Once you are comfortable, a simple /q will do;
4. Don’t forget /SQLSYSADMINACCOUNTS. Remember to put that piece in so you can connect after the installation is complete. Kinda important, ya know.

E:\Installers\SQL>Setup.exe /qs /ACTION=Install /FEATURES=SQL,Tools /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="NT AUTHORITY\SYSTEM"
/SQLSYSADMINACCOUNTS=".\Administrator" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /IACCEPTSQLSERVERLICENSETERMS

To benchmark IO on Linux and MySQL transaction processing, SysBench is a popular choice that can do both. After poking around at the source code, it seems PostgreSQL and Oracle are also included for transaction processing testing if you have the proper header files, but I didn’t test those.

To benchmark IO on Windows and SQL Server transaction processing, Microsoft provides two tools, SQLIO and SQLIOSim. SQLIO is a misnomer in that it really doesn’t have much to do with SQL Server. It is a general purpose disk IO benchmark tool.

So today I was playing with SysBench and noticed that I can compile and build it on Windows as well. I decided I should run IO benchmark on a single machine with both tools (SQLIO and SysBench), and see if I could reconcile the results.

To make things simple, I thought I would just benchmark random read of 3G (orders of magnitude bigger than disk controller cache) files for 5 minutes (300 seconds) with a single thread using 16Kb block size, without adding any additional queue. I tested this on both my laptop and an Amazon EC2 instance. The commands for both tools are listed below, and they should perform the same thing, as far as I can tell. Let me know if you have any comments/pointers or if I missed anything.

SysBench commands:

sysbench --test=fileio --file-total-size=3G prepare
sysbench.exe --test=fileio --file-total-size=3G --file-test-mode=rndrd --max-time=300 run

Fro SQLIO, here is the line in param.txt and command used:

c:\testfile.dat 1 0x0 3072
sqlio -kR -s300 -dc -b16 -frandom -Fparam.txt

As this is a quick test, I ran the same test twice and took the average value for comparison purposes. The detailed output is pasted at the end of this post.

On my Windows XP Pro Service Pack 2 laptop with Intel X-25 SSD:

So on my laptop, SQLIO’s results are 13% higher than that of SysBench.

On Amazon EC2 ami-c3e40daa with EBS device running Windows Server 2008 Datacenter Edition Service Pack 2, whose results varied widely between my two runs:

On this machine, SQLIO results are 66% higher than that of SysBench.

Below is the gory details.

Here are the detailed output on my laptop:
SQLIO
C:\Program Files\SQLIO>sqlio -kR -s300 -dc -b16 -frandom -Fparam.txt
sqlio v1.5.SG
parameter file used: param.txt
file c:\testfile.dat with 1 thread (0) using mask 0×0 (0)
1 thread reading for 300 secs from file c:\testfile.dat
using 16KB random IOs
using specified size: 3072 MB for file: c:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 3835.39
MBs/sec: 59.92

C:\Program Files\SQLIO>sqlio -kR -s300 -dc -b16 -frandom -Fparam.txt
sqlio v1.5.SG
parameter file used: param.txt
file c:\testfile.dat with 1 thread (0) using mask 0×0 (0)
1 thread reading for 300 secs from file c:\testfile.dat
using 16KB random IOs
using specified size: 3072 MB for file: c:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 3832.00
MBs/sec: 59.87

SysBench
C:\MessAround\sysbench-0.4.12\sysbench-0.4.12\sysbench\RelWithDebInfo>sysbench.e
xe –test=fileio –file-total-size=3G –file-test-mode=rndrd –max-time=300 run
sysbench 0.4: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 0
128 files, 24Mb each
3Gb total file size
Block size 16Kb
Number of random requests for random IO: 10000
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
WARNING: Operation time (18446744073709226000.000000) is greater than maximal co
unted value, counting as 10000000000000.000000
WARNING: Percentile statistics will be inaccurate
Done.

Operations performed: 10000 Read, 0 Write, 0 Other = 10000 Total
Read 156.25Mb Written 0b Total transferred 156.25Mb (52.143Mb/sec)
3337.16 Requests/sec executed

Test execution summary:
total time: 2.9966s
total number of events: 10000
total time taken by event execution: 2.9343
per-request statistics:
min: 0.01ms
avg: 0.29ms
max: 18446744073709.47ms
approx. 95 percentile: 0.48ms

Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 2.9343/0.00

C:\MessAround\sysbench-0.4.12\sysbench-0.4.12\sysbench\RelWithDebInfo>sysbench.e
xe –test=fileio –file-total-size=3G –file-test-mode=rndrd –max-time=300 run
sysbench 0.4: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 0
128 files, 24Mb each
3Gb total file size
Block size 16Kb
Number of random requests for random IO: 10000
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
WARNING: Operation time (18446744073694841000.000000) is greater than maximal co
unted value, counting as 10000000000000.000000
WARNING: Percentile statistics will be inaccurate
Done.

Operations performed: 10000 Read, 0 Write, 0 Other = 10000 Total
Read 156.25Mb Written 0b Total transferred 156.25Mb (53.818Mb/sec)
3444.38 Requests/sec executed

Test execution summary:
total time: 2.9033s
total number of events: 10000
total time taken by event execution: 2.8777
per-request statistics:
min: 0.01ms
avg: 0.29ms
max: 18446744073696.34ms
approx. 95 percentile: 15.39ms

Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 2.8777/0.00

Here are the detailed output from Amazon EC2 ami-c3e40daa with EBS device:
SQLIO
c:\Program Files\SQLIO>sqlio -kR -t1 -s300 -dC -frandom -b16 -Fparam.txt -BH -LS

sqlio v1.5.SG
using system counter for latency timings, 3579545 counts per second
parameter file used: param.txt
file c:\testfile.dat with 1 thread (0) using mask 0×0 (0)
1 thread reading for 300 secs from file c:\testfile.dat
using 16KB random IOs
buffering set to use hardware disk cache (but not file cache)
size of file c:\testfile.dat needs to be: 3221225472 bytes
current file size: 0 bytes
need to expand by: 3221225472 bytes
expanding c:\testfile.dat … done.
using specified size: 3072 MB for file: c:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 1230.94
MBs/sec: 19.23
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 0
Max_Latency(ms): 204
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 98 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

c:\Program Files\SQLIO>sqlio -kR -t1 -s300 -dC -frandom -b16 -Fparam.txt -BH -LS

sqlio v1.5.SG
using system counter for latency timings, 3579545 counts per second
parameter file used: param.txt
file c:\testfile.dat with 1 thread (0) using mask 0×0 (0)
1 thread reading for 300 secs from file c:\testfile.dat
using 16KB random IOs
buffering set to use hardware disk cache (but not file cache)
using specified size: 3072 MB for file: c:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 126.88
MBs/sec: 1.98
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 7
Max_Latency(ms): 497
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 13 9 0 3 7 8 8 8 8 8 8 8 2 1 1 1 1 1 1 1 1 0 0 0 2

C:\Users\Administrator\Documents\sysbench-0.4.12\sysbench\RelWithDebInfo>sysbenc
h.exe –test=fileio –file-total-size=3G –file-test-mode=rndrd –max-time=300 r
un
sysbench 0.4: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 0
128 files, 24Mb each
3Gb total file size
Block size 16Kb
Number of random requests for random IO: 10000
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Done.

Operations performed: 10000 Read, 0 Write, 0 Other = 10000 Total
Read 156.25Mb Written 0b Total transferred 156.25Mb (10.64Mb/sec)
680.95 Requests/sec executed

Test execution summary:
total time: 14.6854s
total number of events: 10000
total time taken by event execution: 14.6048
per-request statistics:
min: 0.01ms
avg: 1.46ms
max: 150.29ms
approx. 95 percentile: 4.77ms

Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 14.6048/0.00

C:\Users\Administrator\Documents\sysbench-0.4.12\sysbench\RelWithDebInfo>sysbenc
h.exe –test=fileio –file-total-size=3G –file-test-mode=rndrd –max-time=300 r
un
sysbench 0.4: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 0
128 files, 24Mb each
3Gb total file size
Block size 16Kb
Number of random requests for random IO: 10000
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Done.

Operations performed: 10000 Read, 0 Write, 0 Other = 10000 Total
Read 156.25Mb Written 0b Total transferred 156.25Mb (2.1371Mb/sec)
136.77 Requests/sec executed

Test execution summary:
total time: 73.1139s
total number of events: 10000
total time taken by event execution: 73.0284
per-request statistics:
min: 0.02ms
avg: 7.30ms
max: 728.84ms
approx. 95 percentile: 23.08ms

Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 73.0284/0.00

In the past, when I read technical books, I tend to skim through them, looking for keywords and only read the part that is relevant at the moment, and move on. Sometimes I would make an attempt at finishing a whole book, but a few months or even years later, I haven’t even finished the first 3 chapters!

I took a different approach recently. Now I’ve set up daily goals to read 50 or more pages or a key section of a technical book, and follow through. I am reading two technical books at the moment: Itzik Ben-Gan et al’s Inside Microsoft SQL Server 2005: T-SQL Querying, and Baron Schwartz et al’s High Performance MySQL. It’s nice to read database books that focus on different vendor implementations (SQL Server, MySQL, Oracle, etc.), because each one explains certain things from a slightly different angle, with slightly different language, and at times this gives you a better feel of the overall picture and clarity to certain key concepts.

Here I am talking about quality technical books, though, because the industry churns out way too much junk. It certainly is a waste of time and money on poorly thought out and written books.

Anyway, today I went through Itzik Ben-Gan’s performance tuning chapter. I used SQL Server 2008′s Management Studio to do tests against a SQL Server 2005 instance. I noticed an interesting change in terminologies: in SQL Server 2008, a bookmark lookup on a table with clustered index is now called Key Lookup, on a table without clustered index is (still) called RID lookup. Here are some screen shots:

Bookmark lookup in SQL Server 2005 on a table with cluster index

Bookmark lookup in SQL Server 2005 on a table without cluster index

Bookmark lookup in SQL Server 2008 on a table with cluster index

Bookmark lookup in SQL Server 2008 on a table without cluster index

To recap, here are the terms used for bookmark lookups in the 3 most recent SQL Server releases:

SQL Server 2000: bookmark lookup
SQL Server 2005: RID lookup on a heap, Clustered Index Seek on a table with clustered index
SQL Server 2008: RID lookup on a heap, Key Lookup on a table with clustered index

SQL Server’s clustered index implementation went through some interesting changes. Prior to SQL Server 7, all non-clustered index contains a pointer to the actual row(s) that has the value of the indexed keys. This pointer (RID, Row ID) physically points to the position on which page in which internal file that row is at. Starting from SQL Server 7, for tables without a clustered index (heap), the implementation stays the same. However, for tables with clustered index, the pointer is the clustered index.

This can potentially have a big impact for bookmark lookup on tables with clustered index. Here is why: to do lookups, SQL Server needs to traverse through clustered index, thus more reads. The number of additional reads this causes depends on the level of clustered index and how many rows the query touches. Suppose the clustered index has 3 levels (root, leaf, and one intermediate level), then a single bookmark lookup will incur 3 additional logical reads. If the query touches 2000 rows, then bookmark lookup will cause 6000 additional reads.

Note I am not bashing against clustered index though. Overall, in my opinion, the benefits of clustered index definitely outweighs its drawbacks. Now this post is getting long and I want to go back to my books, so I will stop here.

Microsoft introduced database snapshot in Sql Server 2005. It is used to keep a point-in-time snapshot of the database state at the time the snapshot was taken. It is also good for reporting purposes when database mirroring is implemented, because while a mirroring partner database is not accessible, one can produce a snapshot of that mirror. And the snapshot can be queried. For example, for a time insensitive report, one can point to the snapshot created from the mirror, thus decrease the load on the principal.

Another potential useful case is during change control. For mission-critical applications, new changes to databases are done during off-peak hours, maintenance window, and/or weekends. It is also important to be able to back out those changes when necessary.

I think database snapshot can be great for the case listed above. Right before the change, a snapshot can be taken, like so:

CREATE DATABASE MyDbSnap ON
(NAME = MyDb, FILENAME = 'c:\MyDb.ss')
AS SNAPSHOT OF MyDb

And if we do need to back out, we can run the script below:

RESTORE DATABASE MyDb FROM DATABASE_SNAPSHOT = 'MyDb'

Obviously database snapshot is not a replacement for regular database backups, but it can be useful in cases like these.

I am reading SQL and Relational Theory by C. J. Date. Baron Schwartz wrote a nice review for it. I am reading the online version, about half way through, so am not sure if it has an accompanying CD with source code. In any case, if you want to play with some SQL code listed in the book, here is the script to generate the tables and rows (or should I say relations at a certain point in time with tuples?)

CREATE TABLE S
   ( SNO    VARCHAR(5)   NOT NULL ,
     SNAME  VARCHAR(25)  NOT NULL ,
     STATUS INTEGER      NOT NULL ,
     CITY   VARCHAR(20)  NOT NULL ,
     UNIQUE ( SNO ) ) ;
 CREATE TABLE P
   ( PNO    VARCHAR(6)   NOT NULL ,
     PNAME  VARCHAR(25)  NOT NULL ,
     COLOR  CHAR(10)     NOT NULL ,
     WEIGHT NUMERIC(5,1) NOT NULL ,
     CITY   VARCHAR(20)  NOT NULL ,
     UNIQUE ( PNO ) ) ;
 CREATE TABLE SP
   ( SNO    VARCHAR(5)   NOT NULL ,
     PNO    VARCHAR(6)   NOT NULL ,
     QTY    INTEGER      NOT NULL ,
     UNIQUE ( SNO , PNO ) ,
     FOREIGN KEY ( SNO )
        REFERENCES S ( SNO ) ,
     FOREIGN KEY ( PNO )
        REFERENCES P ( PNO ) ) ;
INSERT INTO S (SNO, SNAME, STATUS, CITY) VALUES ('S1', 'Smith', 20, 'London');
INSERT INTO S (SNO, SNAME, STATUS, CITY) VALUES ('S2', 'Jones', 10, 'Paris');
INSERT INTO S (SNO, SNAME, STATUS, CITY) VALUES ('S3', 'Blake', 30, 'Paris');
INSERT INTO S (SNO, SNAME, STATUS, CITY) VALUES ('S4', 'Clark', 20, 'London');
INSERT INTO S (SNO, SNAME, STATUS, CITY) VALUES ('S5', 'Adams', 30, 'Athens');
INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES ('P1', 'Nut', 'Red', 12.0, 'London');
INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES ('P2', 'Bolt', 'Green', 17.0, 'Paris');
INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES ('P3', 'Screw', 'Blue', 17.0, 'Oslo');
INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES ('P4', 'Screw', 'Red', 14.0, 'London');
INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES ('P5', 'Cam', 'Blue', 12.0, 'Paris');
INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES ('P6', 'Cog', 'Red', 19.0, 'London');
INSERT INTO SP (SNO, PNO, QTY) VALUES ('S1', 'P1', 300);
INSERT INTO SP (SNO, PNO, QTY) VALUES ('S1', 'P2', 200);
INSERT INTO SP (SNO, PNO, QTY) VALUES ('S1', 'P3', 400);
INSERT INTO SP (SNO, PNO, QTY) VALUES ('S1', 'P4', 200);
INSERT INTO SP (SNO, PNO, QTY) VALUES ('S1', 'P5', 100);
INSERT INTO SP (SNO, PNO, QTY) VALUES ('S1', 'P6', 100);
INSERT INTO SP (SNO, PNO, QTY) VALUES ('S2', 'P1', 300);
INSERT INTO SP (SNO, PNO, QTY) VALUES ('S2', 'P2', 400);
INSERT INTO SP (SNO, PNO, QTY) VALUES ('S3', 'P2', 200);
INSERT INTO SP (SNO, PNO, QTY) VALUES ('S4', 'P2', 200);
INSERT INTO SP (SNO, PNO, QTY) VALUES ('S4', 'P4', 300);
INSERT INTO SP (SNO, PNO, QTY) VALUES ('S4', 'P5', 400);

Below is a quick script I put together to split one single file that contains many stored procedures into many files that contain just a single stored procedure. It can be easily modified for views, user-defined functions, etc..

Notes:
1. It does put BEGIN and END before and after the procedure creation. Because some script has grant statements in it, and I want to put END right after the procedure creation, therefore there is a variable called $FirstGrant;
2. The new files created will be named like StoredProcedureName.sql.

#!/usr/bin/perl
use strict;
open(MYDATA, "c:\\junk\\MyDb\\AllProc.sql") or
  die("Error: cannot open file 'AllProc.sql'\n");
    my $FirstGrant = 0;
while( my $line = <mydata> ){
if ($line =~ /^create\s+proc/i) {
   close (FILE);
   $FirstGrant = 0;
   (my $filename) = $line =~ /create\s+proce?d?u?r?e?\s+\[?d?b?o?]?\.?\[?(\w+)/i;
   open (FILE, ">", "c:\\junk\\MyDb\\".$filename.".sql");
   print FILE "IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[$filename]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)\n";
   print FILE "BEGIN\n";
   print FILE $line;
}
else {
if (($line =~ /^grant\s+execute/i) && ($FirstGrant == 0)) {
   print FILE "END\n";
   $FirstGrant = 1;
}
   print FILE $line;
}
}
close MYDATA;

Here is a quick hack with Perl to generate insert statements to reproduce data already exist in a given table. It is similar to results mysqldump generates.

A few things worth mentioning here:
1. This script uses a text file, TableList, that contains a list of tables that you want insert statements created. Each line in the list contains the database name and table name, separated by space(s). This is a quick hack, one could easily pass the file name as an input;
2. Obviously the connection information needs to be modified for your own needs;
3. The script does escape single quote in character fields properly;
4. Use
perl ScriptName.pl > c:\MyFolder\MyTableInsert.sql
to collect the results into c:\MyFolder\MyTableInsert.sql file.
5. Thanks to Summit Amar and people who commented on this article for sql script ideas.
6. Update: modified the script so it handles identity field if it is present in a table.

By the way, Microsoft’s new scripting language, PowerShell, borrowed a lot of good stuff from Perl and Unix shell scripting, which is pretty powerful. My friends Yan Pan and Ananthakumar Muthusamy wrote a book, Microsoft SQL Server 2008 Administration with Windows PowerShell, which I helped to tech-edit. If you are looking to learn more automation and scripting tricks with PowerShell, definitely check out that book, which will come out next month.

#!/usr/bin/perl
use strict;
use Win32::SqlServer;
# Get server name from the command line
# my $ServerName = shift or die "Please enter server name as the first only parameter needed";
# my $TableName = "authors";
open(MYDATA, "RefList.txt") or
  die("Error: cannot open file 'RefList.txt'\n");
while( my $line = <mydata> ){
  $line =~ /(\w+)\s+(\w+)/;
  my $db;
  my $table;
  if ($db ne $1){
	print "-----Tables in $1-----\n";
  }
  $db = $1;
  my $sqlsrv = sql_init(".", "sa", "s3cr3t", $1);
  my $HasIdentityColumn = $sqlsrv->sql_one("select objectproperty(object_id('$2'), 'TableHasIdentity')");
  if ($HasIdentityColumn == 1) {
	print "SET IDENTITY_INSERT $2 ON\n";
  }
# Our SQL statement.
  my $stmnt = < <SQLEND;
--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = '$2'
OPEN cursCol
DECLARE \@string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE \@stringData nvarchar(3000) --for storing the data (VALUES) related statement
DECLARE \@dataType nvarchar(1000) --data types returned for respective columns
SET \@string='INSERT '+ '$2' +'('
SET \@stringData=''
DECLARE \@colName nvarchar(50)
FETCH NEXT FROM cursCol INTO \@colName,\@dataType
IF \@\@fetch_status<>0
	begin
	--print 'Table ' + $2 + ' not found, processing skipped.'
	close curscol
	deallocate curscol
	return
END
WHILE \@\@FETCH_STATUS=0
BEGIN
IF \@dataType in ('varchar','char','nchar','nvarchar')
BEGIN
        SET \@stringData=\@stringData+''''+'''+isnull('''''+'''''+REPLACE('+\@colName+','''''+''''','''''+'''''+'''''+''''')+'''''+''''',''NULL'')+'',''+'
END
ELSE
if \@dataType in ('text','ntext') --if the datatype is text or something else
BEGIN
        SET \@stringData=\@stringData+'''''''''+isnull(REPLACE(cast('+\@colName+' as varchar(2000)),'''''+''''','''''+'''''+'''''+'''''),'''')+'''''',''+'
END
ELSE
IF \@dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
	SET \@stringData=\@stringData+'''convert(money,''''''+isnull(cast('+\@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE
IF \@dataType='datetime'
BEGIN
	SET \@stringData=\@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+\@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
END
ELSE
IF \@dataType='image'
BEGIN
	SET \@stringData=\@stringData+'''''''''+isnull(cast(convert(varbinary,'+\@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
	--SET \@stringData=\@stringData+'''''''''+isnull(cast('+\@colName+' as varchar(200)),''0'')+'''''',''+'
	--SET \@stringData=\@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+\@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
	SET \@stringData=\@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+\@colName+')+'''''+''''',''NULL'')+'',''+'
END
SET \@string=\@string+\@colName+','
FETCH NEXT FROM cursCol INTO \@colName,\@dataType
END
DECLARE \@Query nvarchar(4000)
SET \@query ='SELECT '''+substring(\@string,0,len(\@string)) + ') VALUES(''+ ' + substring(\@stringData,0,len(\@stringData)-2)+'''+'')'' Col1 FROM '+ '$2'
exec sp_executesql \@query
CLOSE cursCol
DEALLOCATE cursCol
SQLEND
# Run query. The return value is a reference to an array.
  my $result = $sqlsrv->sql($stmnt);
# Print results. Each array entry is a hash reference.
  foreach my $row (@$result) {
	print "$$row{Col1}\n";
  }
  if ($HasIdentityColumn == 1) {
	print "SET IDENTITY_INSERT $2 OFF\n";
  }
}
close MYDATA;