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
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
Comments
Post a Comment
Plz dont forget to like Facebook Page..
https://www.facebook.com/pages/Sql-DBAcoin/523110684456757