please test my script to analyze table keys (was "Submitted for your review...:)

Here is a script I wrote that analyzes datasets and returns all the minimal composite and unary keys that uniquely identify records. I wrote it because I frequently have to analyze client spreadsheets and non-normalized data tables.

On my desktop server it took about two minutes to analyze 2000 permutations of a table with 50 columns and 5000 records.

Please try it out for me and let me know if it chokes on anything, or if you see any ways it could be improved!Dude...still looking at it...

Only 2 minutes you say....

hmmmmmmmmmmmmmm

EDIT: I ran it against Nothwind And I got Nothing|||4 minutes 28 seconds on my desktop

Candidate Fields Permutations Checked
------ -------
61 1891

It only returned my identity column.|||Dooh..

It's for one table at a time...I thought you were doing an entire database

OK, I did Products in Northwind and it didn't pick ProductName as a Natural Key...

Candidate Fields Permutations Checked
10 66

Natural Keys Found
[UnitPrice], [UnitsInStock]
[SupplierID], [UnitPrice], [ReorderLevel]
[SupplierID], [UnitsInStock], [ReorderLevel]
[UnitPrice], [UnitsOnOrder], [ReorderLevel]|||Yowch!

Obviously a bug. I will look into it.

Thanks, Brett!|||It may be because there is no unique index on PoductName...but the data is all unique in the sample...

Go figure M$... I wonder if it was done on purpose to show the "Benefits" of Surrogate keys...

"An Apple is an Apple until it's renamed"|||No, its a bug in my script.

The point of the script is to find natural keys, whether or not they have been defined that way on the table. Otherwise, I'd just query sysindexes.

There is a flaw in the recursive logic which I need to track down. If I comment out a clause meant to eliminate redundant branches, the script returns the correct results, but if I don't eliminate redundant branches then I am reduced to testing every permutation, which is impractical.

The solution will probably occur to me in the car on the way home tonight.

Have a good weekend, all.|||I tried to challenge your logic by going after statistics info rather than doing "select count(distinct [fieldname])..." But got drawn into trying to come up with an algorythm:

Once you get all non-text/image columns into a temp table (I also eliminated sql_variant by doing nullif(prec, 0) is not null), instead of doing a cursor I was thinking to create permutations by doing "order by newid()" within the loop with "top @.number_of_qualifying_columns"... And of course, as usual, got distracted, never finished, etc.

Have you thought of that?|||I've tried two different methods of searching permutations. The first was "bottom up", starting with single columns and then adding from there, but required an exhaustive search.

I'm hoping that by using the "top down" approach that I posted I can identify and eliminate searching branches of permutations that are known not to contain natural keys, or that already contain a subset known to be a natural key.

The algorithm used to create the permutations is not as difficult or imporatant as the algorithm used to eliminate permutations.

I have an idea in the back of my head (which did occur to me in the car on the way home!), and I'm trying to come up with a way to implement it.

I think it is an interesting challenge which would also prove useful to solve, so I'm surprised I haven't seen it done before.

Anybody else here is welcome to take a shot at it! Just write a script that efficiently identifies all the unary and minimal composite keys in a table.

Please help: insert into table in cursor-loop?

Hi, all,

I'd like to insert returned items into the result table @.r:

Create function get_items

returns @.r table(a1 varchar(30),a2 varchar(30),a3 varchar(30),a4 varchar(30),a5 varchar(30))

as

begin

declare @.v_item nvarchar (30);

declare @.v_count int;

declare cur_items cursor for

select top 5 a.item from itemtable a; -- gets max. 5 items!

open cur_items;

fetch next from cur_items into @.v_item;

set @.v_count = 0;

while (@.@.fetch_status = 0)

begin

set @.v_count = @.v_count+1;

-- Problem:

-- Insert into @.r(a1,a2...) values(@.v_item)... ?

--

fetch next from cur_items into @.v_item;

end;

close cur_items;

DEALLOCATE cur_items;

return

END

=========

That means,

if @.v_count = 1,

@.r has only one item, such as: 'item1', <null>, <null>, <null>, <null>

but if @.v_count = 5,

@.r has full-row, such as: 'item1', 'item2', 'item3', 'item4', 'item5'

Thank you very much in advance!

If I understand your problem correctly, you don't need a cursor. Use a table valued function (TVF).

Something like this:

CREATE FUNCTION Get_Items ()
RETURNS table
AS
RETURN
( SELECT TOP 5 Item
FROM ItemTable
)

GO

|||

Hello, Arnie Rowland, thanks for your answer!

In my code I have to use the cursor, the definition of the cursor above is just example,

and the result from the cursor is: 'item1', 'item2'... or more, but max. 5 items.

Best regards

|||

Hi, all,

maybe I have to convert all rows of the result table to one row?

after execute the function I got e.g. 3 rows:

item1

item2

item3

==>

if I can convert them to one row, then I have:

item1 | item2 | item3 | <null> | <null>

How can I get it?

Best regards

|||

Here are some resources that may help you get your desired output.

I highly recommend NOT using a cursor if at all possible -and in almost all data retrieval situations, it is possible!

Lists -Field Concatenation
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e
http://milambda.blogspot.com/2005/07/return-related-values-as-array.html

Lists -Field Concatenation( For SQL 2000 & 2005 )
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/7e5b4c8a9b9b968a

Lists -Field Concatenation, One Field to Itself for string
SQL 2000 http://omnibuzz-sql.blogspot.com/2006/06/concatenate-values-in-column-in-sql.html
SQL 2005 http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
http://www.projectdmx.com/tsql/rowconcatenate.aspx

Lists -Recursive Queries
http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=9
http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03i8.asp
http://www.wwwcoder.com/main/parentid/191/site/1857/68/default.aspx
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp