Has some one come up with a similar type script that could be used in a
Postgresql database?
The script below was created for a SQLServer database.
Thx,
-Martin
++++++++++++++++++++++++++++++++++++++
http://searchdatabase.techtarget.com...913717,00.html
In the early stages of application design DBA or a developer creates a data
model. Unfortunately many models work very well with a handful of rows but
fail miserably when the application grows by leaps and bounds. This is why
it is important to populate your data model with data and stress test it
prior to making it available for users. Test data doesn't have to be
perfect; indeed, you can duplicate the same record, or a few different
records, to test the performance of your queries.
This article offers a script for generating INSERT statements for every
table in your database. The script is fairly simple -- it relies on three
system tables: sysusers, sysobjects and syscolumns. It generates an INSERT
statement duplicating the top row in your table. However, it can be easily
altered to fit your needs.
SET NOCOUNT ON
DECLARE @table VARCHAR(200),
@owner VARCHAR(100),
@sql VARCHAR(2000),
@sql1 VARCHAR(2000)
DECLARE @schema TABLE (
table_name VARCHAR(200),
column_name VARCHAR(200))
INSERT @schema
SELECT c.name + '.' + a.name, b.name FROM sysobjects a INNER JOIN
syscolumns b ON b.id = a.id
AND a.type = 'u'
AND a.name <> 'dtproperties'
INNER JOIN sysusers c ON c.uid = a.uid
ORDER BY a.name, b.colid
DECLARE table_cursor CURSOR FOR
SELECT DISTINCT table_name FROM @schema
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql1 = ''
SELECT @sql1 = @sql1 + ', '+ column_name FROM @schema
WHERE table_name = @table
SELECT @sql1 = SUBSTRING(@sql1, 3, LEN(@sql1)-2)
SELECT @sql = 'INSERT ' + @table + '( ' + @sql1 + ' ) ' + CHAR(10) + '
SELECT TOP 1 ' + @sql1 + ' FROM ' +
@table
SELECT @sql
FETCH NEXT FROM table_cursor INTO @table
END
CLOSE table_cursor
DEALLOCATE table_cursor
In the pubs database, the output will be similar to the following:
INSERT dbo.authors( au_id, au_lname, au_fname, phone, address, city, state,
zip, contract )
SELECT TOP 1 au_id, au_lname, au_fname, phone, address, city, state, zip,
contract FROM dbo.authors
INSERT dbo.discounts( discounttype, stor_id, lowqty, highqty, discount )
SELECT TOP 1 discounttype, stor_id, lowqty, highqty, discount FROM
dbo.discounts
INSERT dbo.employee( emp_id, fname, minit, lname, job_id, job_lvl, pub_id,
hire_date )
SELECT TOP 1 emp_id, fname, minit, lname, job_id, job_lvl, pub_id,
hire_date FROM dbo.employee
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
