Thanks, Michael and Larry. I'll test right away -- between suggestions from the two of you I should be able to do what I wan.


And Michael, I'm trying determine ID columns dynamically for the following reason:

There is a real server out there we use of about 200 gigs. Since our test server was glommed, my only test server is a small 120 evaluation copy I've put on my local machine -- and I don't have anything like 200 gigs. So I need to get test data -- a few hundred records from each table.

I thought about going into enterprise manager, and setting up a replication, or backup or dts query for every table in every database. Given the number of databases we have, the number of tables we have, and how often new tables are added and old tables dropped, I did not think about these options very long.

Instead I decided to write a utility that would data from a populated database,and generate a script that would populate an empty with identical structure sample data from the live one. My utility will include a choice of sampling algorithms and also include the a threshold for number of records below which the entire table will be taken.

So I need to spot identity columns on the fly for two reasons.
1) Bringing identity column data over to a new database unchanged requires special handling -- and resetting of the seed in the target database after bringing it over.
2) Since SQL server does not have RECNO() -- I'm going use any of the following which exist instead for my sampling algorithm (with the ones I list first being highest priority)
A) primary key
B) identity column
C) Unique columns
D) Guid columns
E) natural order at time of query

The sampling algorith choices will be:
1) Top N, Bottom N, both top and bottom N
2) Every Nth record (Actually the user gives a target max, the we select evenly across the file)
3) Blocks every Nth record. That is take the first 10, skip 100, take the next 10, skip 100. The user selects block size and number of records to include in sample. The advantage of this over just every nth is that you see patters across muliple tuples -- for example if unwanted duplications are happening in certain circumstances.

When this utility is done, I will post it on the UT

If there is some enterprise manager feature that makes this unneccesary, I'd love to hear about. But remember the ability to add queries in DTS or replication manager is not by itself a solution. I'm doing stuff globally, still somewhat match to the structure and quantity of data in each table.

Thanks again

Gar