How do I find natural primary key suggestions for all the tables in a SQL Server database using a script or procedure?

I have just inherited a SQL Server 2005 application with 200+ tables, none of which have indexes or primary keys, but they all have data in them.  I wanted to see if anyone has a script or stored procedure that could identify suggestions for natural primary keys on the tables based on this criteria.

If you can perform a SELECT Count(*) FROM TABLE (or get the record count for the table in another way) and that equals the record count of a  SELECT DISTINCT [TestColumn1], [TestColum2] FROM TABLE then the columns used in the SELECT DISTINCT combination were good candidates for a natural key.

It seems that the procedure would need to loop through all the columns in a table trying to find a combination of columns that yields the same record count as a SELECT * from the table.  In the tables sometimes have 20+ columns you can see how this could be an issue without some kind of way to automate the suggestion of natural primary keys.

Any help would greatly greatly be appreciated.
endrecAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris MConsulting - Technology ServicesCommented:
Alternatively run this query:

Select [name] AS table_name
from sys.tables
Where Objectproperty(object_id,'TableHasPrimaryKey') <> 0
0
Chris MConsulting - Technology ServicesCommented:
My previous posts show you ways to find all tables in a database with primary keys.

Please note that is you run a "SELECT Count(*) FROM TABLE" and compare results with those from a query like "SELECT DISTINCT [TestColumn1], [TestColum2] FROM TABLE " would be a good way to find candidate keys for as long as the data is not being updated.

This however does not imply that the identified columns have primary key or unique key constraints so you still will need my previous posts to identify those tables with primary keys.

If you intend to run this query to find primary keys on both SQL 2005 and SQL 2000 instances, attached is a snippet to handle both.

Enjoy!



Select [name] as "Table With Primary KEy"
from SysObjects where xtype='U' and
id in
(
Select parent_obj from SysObjects where xtype='PK'
)

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Chris MConsulting - Technology ServicesCommented:
To find tables with no indexes on SQl 2005 and above, here's the script for you (see snippet)

select * from sys.tables t
where not exists(select 1 from sys.indexes i where t.object_id = i.object_id and i.type_desc <> 'heap')

Open in new window

0
endrecAuthor Commented:
Hello Chris,
None of the tables have primary keys or indexes on them already.

So what I want to find is a way to determine which columns are good candidates to become indexes or primary keys, specifically natural primary keys...this would require testing something like a SELECT DISTINCT using combinations of columns until the minimum number of columns that yields the same result as a SELECT * on the same table is reached.
0
Brendt HessSenior DBACommented:
For single column natural keys, a simple script that goes through all of the tables in the DB, column by column, and outputs those columns where COUNT(Distinct MyColumn) = Count(*) for the table.  For compound natural keys, this could take a significantly larger amount of time and resources than you may want to dedicate - consider a query on all possible combinations of columns on all of your tables.

This could be enhances by excluding NULLable columns, and any other column type that you know will not work for this purpose (e.g. bit, char(1), etc).  (Although you can make a PK out of a CHAR(1) in the right circumstances.)

0
Chris MConsulting - Technology ServicesCommented:
I think it's more important for you to index these tables that enforcinf constraints like primary keys when the tables are not meant to have primary keys.

Remember database constraints enforce business rules!
You're likely to disappoint your organization in case you enforce primary keys on tables which do not need them.

For now, i suggest that you index the tables for good performance and then sit down with the business to deliver proper business requirements.

If data is meant ot be duplicated, then that's it, no need to force PKs,
If you still feel you need to enforce PKs, then you could add an extra column like ID int, make it an auto ID and make it a primary key for all tables. (Be careful as this changed the results of queries like "SELECT * FROM ....)

That's my recommendation.
Do not do what you're not required of, just do what your Business wants and then optimise it. If you feel you need to advise the business in terms of normalisation, I suggest that you take this up as a project involving all stakeholders and ensure that all business needs 9of course your needs too) are fully met.

Take down to a test environment (test database0 and then if there are no issues, backup your live database first and then apply the new structual changes outside business hours.

Caution: do not change things for now otherwise you might break scripts and cause an application crashe. I have once hear someone say "When life gives you a lemon, fire the DBA!!" You still need your job :-)

All the best upon your new set of tasks.
0
8080_DiverCommented:
Y'all,
The OP has already stated that the tables have no primary keys or indexes, so it is pointless to provide scripts that find the tables that have or don't have PK's or indexes.
As for the issue of the business getting upset becuase you put a PK on a table, well, if nothing else, you can always add an Identity (INT) column and use that for the PK until you can identify a really good candidate for a Unique Key or, at the very least , an index.  (Among other problems, without any sort of unique key/index, there is no way to identify a given row if you need to delete it.)
If data is meant ot be duplicated, then that's it, no need to force PKs
If data is meant to be duplicated, what does that have to do with whether or not you ut a PK on it?  Are you assuming that a PK always has to be a "natural key" 9i.e. a part of the data instead of an arbitrary integer)?  WRONG! The purpose of the PK is usually to provide the means by which the data is clustered (i.e. physically sorted) as well as identifying a specific row.
If there are no indexes, every access is going to, by definition, use a full table scan . . . which means reading the table row by bloody ro until the desired row is found.  Adding indexes, even non-unique ones, will improve that.  You may still scan the index but it will probably be a partial index scan or an index range scan.  
Look at the queries that access the tables and, more specifically, at the WHERE clauses.  If a table is consistently or frequently accessed via WHERE clauses that involve a set of 2 or 3 columns (maybe in the same order each time or maybe in various orders), then those are probably good candidates for a composite index; however, you woud probably want to get the application code changed so that they all use the same order for those columns in the WHERE clause.
If you see joins between tables consistently beig made on the same columns, then those columns are good candidates for indexes on both tables.
Check out the (graphic) Estimated Query Plans (or the actual ones from executing the queries) and see where there are fat lines joining the little icons, those are areas that may offer optimization opportunities.  Look at the statistics from hovering the mouse over those icons . . . if you see something like "Estimated Number of Rows:  362,765" and you know you are only looking for 1, then look at the description.  Of the description doesn't include the word "Index" but does read something like "Table Scan", then a) the query isn't using an index and b) it needs one. ;-)
Look at the arrows going from one icon to the next one.  If one is fat and the other skinny, then look at the icon that the fat arrow comes from . . . it is a potential problem.
0
8080_DiverCommented:
Also, if you do the comparison trick, that may point to a potential PK Candidate.  However, you may have to look at composite PK's (ones made up of more than one column) and you may want to consider whether or not it makes sense to create a Clustered or Nonclustered PK (there can only be at most one clustered index on a table but there doesn't have to be one, even if you have a PK ;-).
Think about how the data is inserted, updated, and deleted.  If the those are fairly random with regards to what you are considering for a PK, then you shouldn't cluster the PK . . . and you may want to consider using that column (or those columns) as a Unique Index and one or more other columns as the clustered index (even if not a PK).
For instance, if the data is entered with a datetime column, clustering on the datetime column and one or more other columns may prevent the fragmenting that using, for instance, an Account Number, Order Number, Item Number might create.  (This is why it sometimes makes some sense to use an Identity column as a PK and then use other columns as indexes . . . Identities are guaranteed to be steadily increasing and, therefore, the new data is appended to the table without fragmenting it.)
A lot of the indexing and PK set up, though, is going to depend on how the data is actually accessed.  That's why I said to look at the WHERE clauses. ;-)
0
endrecAuthor Commented:
The application is essentially closed source think of it as a black hole but the database could be modified by adding indexes or primary keys with the exact logic I mentioned.  We know that every table has a natural primary key that could be indentified, but to do that we need a way to take a table and loop through each combination of columns to find the minimum number of columns that a unique index or primary key could be created on.

The reason this needs to be done is ecause if we have a primary key added to each table then we can use a code generator to build a data access layer to the tables (they generally all require a pk to do this).

If we add the PK/indexes based on the logic in my original post we are fairly sure it would speed up the existing app.  Doing this manually for a few tables already has.  The. If we have a data access layer we could start to build our way out of the app's blackhole-ish closed source nature while keeping it running.

I just need to know how to loop through the columns in a table, build and test a dynamic select distinct based on a combination of the columns, and see if a select * (or somethig that gets the record count) equals the same record count as the select distinct.  If it does stop trying to did combinations and just output the column names.
0
LowfatspreadCommented:
TRY SOMETHING LIKE THIS...

keep adding additional INSERTS TO THE TEMP TABLE TO TEST OUT FURTHER COLUMN COMBINATIONS

THEN  EXECUTE THE GENERATED QUERIES...
DROP TABLE #TEMP

 select A.column_name  +','+B.COLUMN_NAME AS COLs
      ,'['+a.table_schema+'].['+a.table_name+']' as tabname
   INTO #TEMP
  from (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
         WHERE IS_NULLABLE = 'NO' 
           AND DATA_TYPE NOT IN ('BIT','TEXT','NTEXT','IMAGE')
           ) as a
  inner join (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
         WHERE IS_NULLABLE = 'NO'
           AND DATA_TYPE NOT IN ('BIT','TEXT','NTEXT','IMAGE')
           ) as b
    on a.TABLE_NAME=b.TABLE_NAME
   AND A.TABLE_SCHEMA=B.TABLE_SCHEMA
   AND A.ORDINAL_POSITION<B.ORDINAL_POSITION 
-- where a.TABLE_NAME like 'tr%' 
 ORDER BY A.ORDINAL_POSITION,B.ORDINAL_POSITION
 
 INSERT INTO #TEMP (COLS,TABNAME)
 select A.column_name  
        +','+B.COLUMN_NAME 
        +','+C.COLUMN_NAME 
        AS COLs
      ,'['+a.table_schema+'].['+a.table_name+']' as tabname
   
  from (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
         WHERE IS_NULLABLE = 'NO' 
           AND DATA_TYPE NOT IN ('BIT','TEXT','NTEXT','IMAGE')
           ) as a
  inner join (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
         WHERE IS_NULLABLE = 'NO'
           AND DATA_TYPE NOT IN ('BIT','TEXT','NTEXT','IMAGE')
           ) as b
     on a.TABLE_NAME=b.TABLE_NAME
   AND A.TABLE_SCHEMA=B.TABLE_SCHEMA
   AND A.ORDINAL_POSITION<B.ORDINAL_POSITION        
  inner join (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
         WHERE IS_NULLABLE = 'NO'
           AND DATA_TYPE NOT IN ('BIT','TEXT','NTEXT','IMAGE')
           ) as C         
    on a.TABLE_NAME=C.TABLE_NAME
   AND A.TABLE_SCHEMA=C.TABLE_SCHEMA
   AND b.ORDINAL_POSITION<C.ORDINAL_POSITION 
 --where a.TABLE_NAME like 'tr%' 
 ORDER BY A.ORDINAL_POSITION,B.ORDINAL_POSITION,C.ORDINAL_POSITION
 
 SELECT 'Select 100.00*Y.[&@#count]/Z.[&@#ROWS] AS [CARD%],Z.[&@#ROWS] AS rOWS,[&@#count] AS CARD,AVGDUPS,MAXDUPS,MINDUPS,'  
          + ''''+tabname+''' AS TAB,'''+cols+''' as candkey from '
          + '(select count(*) as [&@#count],MAX([&@#count]) AS MAXDUPS,avg([&@#count]) AS avgDUPS,MIN([&@#count]) AS mINDUPS from ('
          + 'Select '+cols+',count(*) as [&@#count] from '+tabname+' group by '+cols 
          + ') as X) as Y,(SELECT COUNT(*) AS [&@#ROWS] FROM '+TABNAME+') AS z'
   FROM #TEMP

Open in new window

0
LowfatspreadCommented:
E.G  
RESULT OF GENERATION EXECUTION


(6 row(s) affected)

(2 row(s) affected)

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Select 100.00*Y.[&@#count]/Z.[&@#ROWS] AS [CARD%],Z.[&@#ROWS] AS rOWS,[&@#count] AS CARD,AVGDUPS,MAXDUPS,MINDUPS,'[dbo].[tr_test]' AS TAB,'a,b,c' as candkey from (select count(*) as [&@#count],MAX([&@#count]) AS MAXDUPS,avg([&@#count]) AS avgDUPS,MIN([&@#count]) AS mINDUPS from (Select a,b,c,count(*) as [&@#count] from [dbo].[tr_test] group by a,b,c) as X) as Y,(SELECT COUNT(*) AS [&@#ROWS] FROM [dbo].[tr_test]) AS z
Select 100.00*Y.[&@#count]/Z.[&@#ROWS] AS [CARD%],Z.[&@#ROWS] AS rOWS,[&@#count] AS CARD,AVGDUPS,MAXDUPS,MINDUPS,'[dbo].[Temp_Table5]' AS TAB,'X,Y,Z' as candkey from (select count(*) as [&@#count],MAX([&@#count]) AS MAXDUPS,avg([&@#count]) AS avgDUPS,MIN([&@#count]) AS mINDUPS from (Select X,Y,Z,count(*) as [&@#count] from [dbo].[Temp_Table5] group by X,Y,Z) as X) as Y,(SELECT COUNT(*) AS [&@#ROWS] FROM [dbo].[Temp_Table5]) AS z
Select 100.00*Y.[&@#count]/Z.[&@#ROWS] AS [CARD%],Z.[&@#ROWS] AS rOWS,[&@#count] AS CARD,AVGDUPS,MAXDUPS,MINDUPS,'[dbo].[tr_test]' AS TAB,'a,b' as candkey from (select count(*) as [&@#count],MAX([&@#count]) AS MAXDUPS,avg([&@#count]) AS avgDUPS,MIN([&@#count]) AS mINDUPS from (Select a,b,count(*) as [&@#count] from [dbo].[tr_test] group by a,b) as X) as Y,(SELECT COUNT(*) AS [&@#ROWS] FROM [dbo].[tr_test]) AS z
Select 100.00*Y.[&@#count]/Z.[&@#ROWS] AS [CARD%],Z.[&@#ROWS] AS rOWS,[&@#count] AS CARD,AVGDUPS,MAXDUPS,MINDUPS,'[dbo].[Temp_Table5]' AS TAB,'X,Y' as candkey from (select count(*) as [&@#count],MAX([&@#count]) AS MAXDUPS,avg([&@#count]) AS avgDUPS,MIN([&@#count]) AS mINDUPS from (Select X,Y,count(*) as [&@#count] from [dbo].[Temp_Table5] group by X,Y) as X) as Y,(SELECT COUNT(*) AS [&@#ROWS] FROM [dbo].[Temp_Table5]) AS z
Select 100.00*Y.[&@#count]/Z.[&@#ROWS] AS [CARD%],Z.[&@#ROWS] AS rOWS,[&@#count] AS CARD,AVGDUPS,MAXDUPS,MINDUPS,'[dbo].[Temp_Table5]' AS TAB,'X,Z' as candkey from (select count(*) as [&@#count],MAX([&@#count]) AS MAXDUPS,avg([&@#count]) AS avgDUPS,MIN([&@#count]) AS mINDUPS from (Select X,Z,count(*) as [&@#count] from [dbo].[Temp_Table5] group by X,Z) as X) as Y,(SELECT COUNT(*) AS [&@#ROWS] FROM [dbo].[Temp_Table5]) AS z
Select 100.00*Y.[&@#count]/Z.[&@#ROWS] AS [CARD%],Z.[&@#ROWS] AS rOWS,[&@#count] AS CARD,AVGDUPS,MAXDUPS,MINDUPS,'[dbo].[tr_test]' AS TAB,'a,c' as candkey from (select count(*) as [&@#count],MAX([&@#count]) AS MAXDUPS,avg([&@#count]) AS avgDUPS,MIN([&@#count]) AS mINDUPS from (Select a,c,count(*) as [&@#count] from [dbo].[tr_test] group by a,c) as X) as Y,(SELECT COUNT(*) AS [&@#ROWS] FROM [dbo].[tr_test]) AS z
Select 100.00*Y.[&@#count]/Z.[&@#ROWS] AS [CARD%],Z.[&@#ROWS] AS rOWS,[&@#count] AS CARD,AVGDUPS,MAXDUPS,MINDUPS,'[dbo].[tr_test]' AS TAB,'b,c' as candkey from (select count(*) as [&@#count],MAX([&@#count]) AS MAXDUPS,avg([&@#count]) AS avgDUPS,MIN([&@#count]) AS mINDUPS from (Select b,c,count(*) as [&@#count] from [dbo].[tr_test] group by b,c) as X) as Y,(SELECT COUNT(*) AS [&@#ROWS] FROM [dbo].[tr_test]) AS z
Select 100.00*Y.[&@#count]/Z.[&@#ROWS] AS [CARD%],Z.[&@#ROWS] AS rOWS,[&@#count] AS CARD,AVGDUPS,MAXDUPS,MINDUPS,'[dbo].[Temp_Table5]' AS TAB,'Y,Z' as candkey from (select count(*) as [&@#count],MAX([&@#count]) AS MAXDUPS,avg([&@#count]) AS avgDUPS,MIN([&@#count]) AS mINDUPS from (Select Y,Z,count(*) as [&@#count] from [dbo].[Temp_Table5] group by Y,Z) as X) as Y,(SELECT COUNT(*) AS [&@#ROWS] FROM [dbo].[Temp_Table5]) AS z

(8 row(s) affected)


Result of executing the generated queries

CARD%                                   rOWS        CARD        AVGDUPS     MAXDUPS     MINDUPS     TAB             candkey
--------------------------------------- ----------- ----------- ----------- ----------- ----------- --------------- -------
75.0000000000000                        4           3           1           2           1           [dbo].[tr_test] a,b,c

(1 row(s) affected)

CARD%                                   rOWS        CARD        AVGDUPS     MAXDUPS     MINDUPS     TAB                 candkey
--------------------------------------- ----------- ----------- ----------- ----------- ----------- ------------------- -------
100.0000000000000                       10          10          1           1           1           [dbo].[Temp_Table5] X,Y,Z

(1 row(s) affected)

CARD%                                   rOWS        CARD        AVGDUPS     MAXDUPS     MINDUPS     TAB             candkey
--------------------------------------- ----------- ----------- ----------- ----------- ----------- --------------- -------
50.0000000000000                        4           2           2           3           1           [dbo].[tr_test] a,b

(1 row(s) affected)

CARD%                                   rOWS        CARD        AVGDUPS     MAXDUPS     MINDUPS     TAB             candkey
--------------------------------------- ----------- ----------- ----------- ----------- ----------- --------------- -------
75.0000000000000                        4           3           1           2           1           [dbo].[tr_test] a,c

(1 row(s) affected)

CARD%                                   rOWS        CARD        AVGDUPS     MAXDUPS     MINDUPS     TAB             candkey
--------------------------------------- ----------- ----------- ----------- ----------- ----------- --------------- -------
75.0000000000000                        4           3           1           2           1           [dbo].[tr_test] b,c

(1 row(s) affected)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
slight improvement  

bit can be a primary key member
pk must be less than 901 bytes
DROP TABLE #TEMP

 -- character_octet_length -1 is varchar(max) max pk length is 900 bytes   
 select '['+A.column_name  +'],['+B.COLUMN_NAME+']' AS COLs
      ,'['+a.table_schema+'].['+a.table_name+']' as tabname
   INTO #TEMP
  from (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
         WHERE IS_NULLABLE = 'NO' 
           AND DATA_TYPE NOT IN ('TEXT','NTEXT','IMAGE')
           and coalesce(CHARACTER_OCTET_LENGTH,0) between 0 and 900  
           ) as a
  inner join (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
         WHERE IS_NULLABLE = 'NO'
          AND DATA_TYPE NOT IN ('TEXT','NTEXT','IMAGE')         
           and coalesce(CHARACTER_OCTET_LENGTH,0) between 0 and 900  
           ) as b
    on a.TABLE_NAME=b.TABLE_NAME
   AND A.TABLE_SCHEMA=B.TABLE_SCHEMA
   AND A.ORDINAL_POSITION<B.ORDINAL_POSITION 
   where convert(bigint,coalesce(a.character_octet_length,0) )
       + coalesce(b.character_octet_length,0)
        <901
--and a.TABLE_NAME like 'tr%' 
 ORDER BY 2,A.ORDINAL_POSITION,B.ORDINAL_POSITION
 
 INSERT INTO #TEMP (COLS,TABNAME)
 select '['+A.column_name  
        +'],['+B.COLUMN_NAME 
        +'],['+C.COLUMN_NAME
        +']' 
        AS COLs
      ,'['+a.table_schema+'].['+a.table_name+']' as tabname
   
  from (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
         WHERE IS_NULLABLE = 'NO' 
           AND DATA_TYPE NOT IN ('TEXT','NTEXT','IMAGE')         
           and coalesce(CHARACTER_OCTET_LENGTH,0) between 0 and 900   
           ) as a
  inner join (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
         WHERE IS_NULLABLE = 'NO'
           AND DATA_TYPE NOT IN ('TEXT','NTEXT','IMAGE')        
           and coalesce(CHARACTER_OCTET_LENGTH,0) between 0 and 900     
           ) as b
     on a.TABLE_NAME=b.TABLE_NAME
   AND A.TABLE_SCHEMA=B.TABLE_SCHEMA
   AND A.ORDINAL_POSITION<B.ORDINAL_POSITION        
  inner join (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
         WHERE IS_NULLABLE = 'NO'
           AND DATA_TYPE NOT IN ('TEXT','NTEXT','IMAGE')          
           and coalesce(CHARACTER_OCTET_LENGTH,0) between 0 and 900 
          
           ) as C         
    on a.TABLE_NAME=C.TABLE_NAME
   AND A.TABLE_SCHEMA=C.TABLE_SCHEMA
   AND b.ORDINAL_POSITION<C.ORDINAL_POSITION 
 where convert(bigint,coalesce(a.character_octet_length,0)) 
       + coalesce(b.character_octet_length,0)
       + coalesce(c.character_octet_length,0)
        <901  
-- and a.TABLE_NAME like 'tr%' 
 ORDER BY 2,A.ORDINAL_POSITION,B.ORDINAL_POSITION,C.ORDINAL_POSITION
 
 SELECT 'Select 100.00*Y.[&@#count]/Z.[&@#ROWS] AS [CARD%],Z.[&@#ROWS] AS rOWS,[&@#count] AS CARD,AVGDUPS,MAXDUPS,MINDUPS,'  
          + ''''+tabname+''' AS TAB,'''+cols+''' as candkey from '
          + '(select count(*) as [&@#count],MAX([&@#count]) AS MAXDUPS,avg([&@#count]) AS avgDUPS,MIN([&@#count]) AS mINDUPS from ('
          + 'Select '+cols+',count(*) as [&@#count] from '+tabname+' group by '+cols 
          + ') as X) as Y,(SELECT COUNT(*) AS [&@#ROWS] FROM '+TABNAME+') AS z'
   FROM #TEMP
   order by tabname,cols
   

Open in new window

0
endrecAuthor Commented:
Lowfatspread, this is awesome.  I'm getting some divide by zero errors when executing some of the generated SQL, but I'll try it out more on Monday.
0
LowfatspreadCommented:
so some of the tables are empty ( zero rows)

could use following select instead of previous
SELECT 'Select case Z.[&@#ROWS] when 0 then 0.00 else 100.00*Y.[&@#count]/Z.[&@#ROWS] end AS [CARD%],Z.[&@#ROWS] AS rOWS,[&@#count] AS CARD,AVGDUPS,MAXDUPS,MINDUPS,'  
          + ''''+tabname+''' AS TAB,'''+cols+''' as candkey from '
          + '(select count(*) as [&@#count],MAX([&@#count]) AS MAXDUPS,avg([&@#count]) AS avgDUPS,MIN([&@#count]) AS mINDUPS from ('
          + 'Select '+cols+',count(*) as [&@#count] from '+tabname+' group by '+cols 
          + ') as X) as Y,(SELECT COUNT(*) AS [&@#ROWS] FROM '+TABNAME+') AS z'
   FROM #TEMP
   order by tabname,cols
   

Open in new window

0
endrecAuthor Commented:
Thanks LFS.  I did not run the statements on the real database that I know has no tables with 0 records in it, but the DB I tested it on does have tables without records.

0
dportasCommented:
You can use analysis of the data to determine potential keys in the data but I don't think that can ever be a substitute for understanding the meaning and actual use of the data. Data that is unique today may not be tomorrow - unless you know that the intended meaning and the implementation guarantee that it always will be unique.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.