find all tables witout PK but including data

• gerhard.mueller-1049353

  Grasshopper

  Points: 14

  More actions

  December 5, 2013 at 3:44 am

  #280763

  -- 1. This SQL-Statement generates x SQL-Statements for all tables that have ...

  -- a) no Primary Key

  -- b) but include data

  SELECT CASE ROW_NUMBER() OVER (ORDER BY LEN(o.name) desc)

  WHEN 1 THEN 'select count(1) count_of_rows, ''' + o.name + ''' tab_name into #tmp from ' + o.name

  ELSE 'insert into #tmp select count(1) count_of_rows, ''' + o.name + ''' tab_name from ' + o.name

  END

  FROM sys.indexes AS i RIGHT OUTER JOIN sys.sysobjects AS o

  ON i.object_id = OBJECT_ID(o.name) AND i.is_primary_key = 1

  WHERE (o.type = 'U') AND (i.is_primary_key IS NULL)

  ORDER BY LEN(o.name) desc

  -- 2. Now create and fill the #tmp-Table by executing the generated SQL-Statments above ...

  -- select count(1) count_of_rows, '.....' tab_name into #tmp from .....

  -- insert into #tmp select count(1) count_of_rows, '....' tab_name from ....

  -- insert into #tmp select count(1) count_of_rows, '...' tab_name from ...

  -- 3. Select only tables containing row(s)

  select * from #tmp where count_of_rows > 0

  -- 4. clean up

  drop table #tmp