SQL

TSQL Count Number Of Stored Procedures, Views, Tables or Functions

By | Code, Database, MSSQL2005, MSSQL2008, Software Development, SQL Server, TSQL | 16 Comments

You can use TSQL to Count Number Of Stored Procedures, Views, Tables or Functions in a Database by using the Database INFORMATION_SCHEMA view

/* Count Number Of Tables In A Database */
    SELECT COUNT(*) AS TABLE_COUNT FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE='BASE TABLE'
    /* Count Number Of Views In A Database */
    SELECT COUNT(*) AS VIEW_COUNT FROM INFORMATION_SCHEMA.VIEWS
    /* Count Number Of Stored Procedures In A Database */
    SELECT COUNT(*) AS PROCEDURE_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'
    /* Count Number Of Functions In A Database */
    SELECT COUNT(*) AS FUNCTION_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'

The same methodology can be used to query for information :

/* Select Table Information For A Database */
    SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
    /* Select View Information For A Database */
    SELECT * FROM INFORMATION_SCHEMA.VIEWS
    /* Select Stored Procedure Information For A Database */
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'
    /* Select Function Information For A Database */
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'

SQL Server – TSQL DISTINCT or GROUP BY?

By | Code, Database, MSSQL2005, MSSQL2008, Software Development, SQL Server, TSQL | One Comment

DISTINCT v GROUP BY

A DISTINCT and GROUP BY usually generate the same query plan, so performance should be very simular or the same, if you only need to remove the dulicates then DISTINCT is easier to understand in the query, if you have a more complex query it may be worth running both and studdying the execution plan to see if your specific example wins

Take a MSSQL database offline and back online with TSQL

By | Code, Database, MSSQL2005, Software Development, SQL Server, TSQL | One Comment

Take a database offline with TSQL:

— Kill All Other Users Processes And Set The Database To Single User Mode
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
— Take Offline
EXEC sp_dboption ‘AdventureWorks’, ‘offline’, ‘TRUE’

And restoring the database back to online with TSQL:

— Allow Users Back In By Setting Multi User Mode On
ALTER DATABASE AdventureWorks SET MULTI_USER
— Bring Back Online
EXEC sp_dboption ‘AdventureWorks’, ‘offline’, ‘FALSE’