SQL Server Database Audit Queries

When we work with a single table in SQL Server, it is very easy to perform audit queries. But, while dealing with multiple
tables of the same database, it becomes a very tedious job for new database developers. We need data from multiple sources(for
example : all tables of a single database). As a newcomer to this field, i too got stuck in the same kind of queries, so, I
have searched the web and have listed down a few queries that can help in retrieving data through audit queries. 

select COUNT(*) from sys.databases    --to count the total no. of databases hosted on a single server

SELECT COUNT(*) from information_schema.tables WHERE table_type = 'base table'    --to count the total no. of tables in a database

SELECT COUNT(*) AS VIEW_COUNT FROM INFORMATION_SCHEMA.VIEWS    --to count the total no. of views in a database

SELECT COUNT(*) AS PROCEDURE_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'    --to count the total no. Of Stored Procedures in a Database

SELECT COUNT(*) AS FUNCTION_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'    --to count the total no. Of Functions in a Database

SELECT COUNT(*) AS VIEW_COUNT FROM INFORMATION_SCHEMA.VIEWS    --to count the total no. of views in a database

SELECT COUNT(*) AS PROCEDURE_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'    --to count the total no. of stored procedures in a database

SELECT COUNT(*) AS FUNCTION_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'    --to count the total no. of functions in a database

/*  to count the total no. of key contraints in a database  */
select count(*) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS    --contains primary key,foreign key,unique
select count(*) from sys.key_constraints    --doesn't contain foreign key

/*  to count the key constraints alongwith their types in a database  */
select CONSTRAINT_TYPE,count(*) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS group by CONSTRAINT_TYPE    --contains primary key,foreign key,unique
select type_desc,count(*) from sys.key_constraints group by type_desc    --doesn't list foreign key

/*  to count the no. of indexes in a databases  */
select count(*) from sys.indexes    --new approach
select count(*) from sysindexes     --old approach


select type_desc,count(*) from sys.indexes group by type_desc    --to count the total no. of indexes alongwith their types in a databases


select count(*) FROM sys.objects    --to count the total no. of system objects in a database


SELECT type_desc,count(*) FROM sys.objects group by type_desc    --  to count the system objects alongwith their types in a database


/*  to list down all the primary keys in a database alongwith their tables and columns  */
select s.name as TABLE_SCHEMA, t.name as TABLE_NAME, k.name as CONSTRAINT_NAME, c.name as COLUMN_NAME
  from sys.key_constraints as k
  join sys.tables as t
    on t.object_id = k.parent_object_id
  join sys.schemas as s
    on s.schema_id = t.schema_id
  join sys.index_columns as ic
    on ic.object_id = t.object_id
   and ic.index_id = k.unique_index_id
  join sys.columns as c
    on c.object_id = t.object_id
   and c.column_id = ic.column_id
 where k.type = 'PK';

Happy Reading
Gunjan Singh