Microsoft Windows, MSSQL, Tutorials

Find Table Count and Tables without Primary Key in MS SQL

Few days ago while working on MS SQL 2008 Transactional Replication I have faced following two issue
1) To find out the total number of tables to verify that Primary Server and Secondary server have same table count.
2) Tables without Primary key: After replication setup I have found that some tables were not replication. When I checked the logs it showed following error

This table cannot be published because it does not have a primary key column. Primary key columns are required for all tables in transactional publications.

1) Find Number of Tables:

a) Open MSSQL management studio and login using SA password or Administrator access.
b) Click on “New Query” and select the database using following command

use <database name>

 

c) now execute following Query to count the total number of tables in the database.

Select Count(*) As TableCount
From Information_Schema.Tables
Where Table_Type = ‘BASE TABLE’

 

You will get result like:

2) Tables without primary key:

a) Open MSSQL management studio and login using SA password or Administrator access.
b) Click on “New Query” and select the database using following command

use <database name>

 

c) now execute following Query to find the Tables without Primary Key.

SELECT DISTINCT
T.TABLE_CATALOG
, T.TABLE_SCHEMA
, T.TABLE_NAME
FROM
INFORMATION_SCHEMA.TABLES T
LEFT OUTER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON TC.TABLE_NAME = T.TABLE_NAME
AND TC.TABLE_SCHEMA = T.TABLE_SCHEMA
AND TC.CONSTRAINT_TYPE = ‘PRIMARY KEY’
WHERE
TC.TABLE_NAME IS NULL
AND T.TABLE_TYPE = ‘BASE TABLE’

 

Output tab will show result like:

3 Comments

  1. Generally I do not learn article on blogs, but I would like to say that this write-up very pressured me to try and do it! Your writing style has been surprised me. Thank you, quite nice article.

  2. Fantastic blog! Do you possess any recommendations for
    aspiring writers? I’m planning to start out my very own blog soon but I’m just a little lost on everything.
    Would you suggest beginning from a free platform like WordPress or go for a
    paid option? There are so many options around that I’m totally
    confused .. Any tips? Thanks!

  3. Hello! I recently desired to ask if you possess any trouble with hackers?

    My last blog (wordpress) was hacked and so i ended up losing many
    months of perseverance on account of no back
    up. Are you experiencing any solutions to stop hackers?

Leave a Reply