Table

SQL Server Schema,Table,Function and Stored procedure Auditing

By | Code, iPhone, MSSQL2005, MSSQL2008, Objective-C, Software Development, SQL Server, TSQL | One Comment

If you like to know what’s changing on your SQL Server then the following code creates an audit table and the associated trigger for logging the changes

The Audit Object Table

CREATE TABLE [dbo].[AuditObjects](
 [EventID] [INT] IDENTITY(1,1) NOT NULL
,[EventData] [xml] NULL
PRIMARY KEY CLUSTERED (
   [EventID] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

And the associated Trigger

CREATE TRIGGER [TriggerAuditObjects]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO [dbo].[AuditObjects](EventData)
SELECT EVENTDATA()
GO
ENABLE TRIGGER [TriggerAuditObjects] ON DATABASE

You can now use xpath queries to analyse the data

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'