Today, I am going to show how to use magic tables in the SQL server without using a trigger.

In our daily working environments, we need to maintain history or track of master data in transaction tables for a clear understanding of how data changed day by day. We have different approaches to achieve this work like creating triggers, temporal tables, and some other things. But, we achieve without creating triggers and temporal tables in our database. As all we know, there are two magic tables, i.e, inserted, and deleted. We use these in our triggers. In many scenarios, triggers are not preferable because they hold and block a full table when they fired. Today, I came up with a good approach of using those beautiful magic tables in our SQL server database with creating a trigger. For example, I want to insert all values into table2 from table1 after inserting the values in table1 by using a single insert statement.
 
First, I am creating two tables in my database with the following code:
    CREATE TABLE Table1 (ID1 INT, Col1 VARCHAR(100))  
    GO  
    CREATE TABLE Table2 (ID2 INT, Col2 VARCHAR(100))  
    GO  

After that, I am inserting values in table1 by using the insert statement and inserting it into table2 at a time without using another insert statement, but I am using a magic table with the below code:
    INSERT INTO Table1 (ID1, Col1)  
    OUTPUT inserted.ID1, inserted.Col1  
    INTO Table2  
    VALUES(1,'Col'), (2, 'Col2')  
    GO  

Check the result after executing the above code whether the data inserted into a table2 or not using the select statements.
    SELECT *  
    FROM Table1  
    GO  
    SELECT *  
    FROM Table2  
    GO  

I got the output as follows:

In the same way, we can insert fetched records from another table by using a select statement. in General, we use to insert data into a table that is fetched by using the select command. You can observe the code below for a better understanding.
    insert into #temp1(BankAccountTransactionId)  
    output inserted.BankAccountTransactionId into #temp2  
    select BankAccountTransactionId    
    from Bankaccounttransactions (nolock) where transactiondate ='2020-08-01'  
    go  

In the above query, I inserted data into #temp1 that was fetched from my table present in my database. You can observe the output by selecting records from both #temp1 and #temp2 tables.
 
In the same way, we can use our other magic table deleted to track old data. Here I am updating data in col1 column agonist id1 = 1 in table1 and you observe in table2, a record was inserted with the old value of table one. For a clear understanding, truncate data in table2 before going to update the data in table1. Execute the below code.
    update  Table1 set col1 = 'col3'   
    OUTPUT deleted.ID1, deleted.Col1  
    INTO Table2  
    where ID1 = 1  

I am updating the record in col1 column against id1=1. For a better understanding of data changes in the table, I want to track history in table2. I can store old data in table2 by using the above code. The output is as follows:

 
We can also store them in log tables which we were deleted by using a magic table deleted. Generally, we never use such scenarios as the track of deleted records. I want to delete records from #temp1 but I need to store the records in my audit table I;e, #temp2. The below code will help in this scenario.
    delete from Table1  
    OUTPUT deleted.ID1, deleted.Col1  
    INTO Table2  

See the output below by executing the following command:
    SELECT *  
    FROM Table1  
    GO  
    SELECT *  
    FROM Table2  
    GO  

Notice that I can store old data and insert data into multiple tables at a time using a single insert statement without using triggers and temporal tables.

I went for a SQL Developer interview and the interviewer asked me to write code to find how many times a string appeared in a paragraph. For example, consider the below string.
    set @string = 'A stored procedures is a subroutine available to applications that access a relational database management system.     
    Such procedures are stored in the database data dictionary.     
    Uses for stored procedures include data-validation or access-control mechanisms'   

In the above example, I want to find "Procedures" repeated in a paragraph. The word repeated 3 times, and so the output should be 3. Please have a look at the below steps to see how I achieved the desired output.
 
In general, we have heard this type of question in a different manner, like how many times is a letter repeated in a given string. For example; I want to find how many times the letter "a" is repeated in the word "database".
select LEN(@string)-len(REPLACE('database','a',''))
 
After executing the above command we get the output as 3. But, it will not work in my scenario, because, my paragraph has 262 words and the "Procedures" string repeated 3 times and the length of my word is 10. If I execute the above I get the result as 30. So we need to go  a bit further to achieve the correct result. If I divide my output with the length of the word which I need to find we can get the correct result. I am attaching the sample code below, execute that code for better understanding.
    Declare @string varchar(max)    
        
    set @string = 'A stored procedures is a subroutine available to applications that access a relational database management system.     
    Such procedures are stored in the database data dictionary.     
    Uses for stored procedures include data-validation or access-control mechanisms'    
        
    select (LEN(@string)-len(REPLACE(@string,'procedures','')))/LEN('procedures')  

After executing the above code I got the output as follows.

For better understanding please try this with your own examples.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

This is the easiest way to create a connection string to the database without taking too much time. So, many times at the beginner level, I faced problems on the connection string and providers associated with the connection string. Then, I found a way to handle it simply following the below steps. I think it should be shared with you all.
 
Hope you like this simple tutorial!
 
Let's begin with the steps:
 
Step 1
Open Notepad on your PC.
 
Step 2
Now Save the Notepad File with Ctrl+Shift+S as Connection.udl on the desktop.
 
Step 3
Now go to the Desktop and double click on the file Connection.udl. The opened file looks like:

Step 4
Now select or Enter server Name
 
Step 5
Now Select Database Name
 
Step 6
Now Press Test Connection.
 
Step 7
Click OK on Test Connection PopUp and Click OK on the Connection.udl File.
 
Step 8
Then open the Connection.udl file with Notepad, then you get the Database Connection String.

Note
This Connection.udl file only create when it is not on your system by following Step1 to Step2.
Otherwise, you can continue with From Step 3 to Step 8. Those who are unable to create a Connection.udl file, please find the attachment, and continue with Step 3 to Step 8.

HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In my opinion, SQL Server 2019 is one of the greatest releases of the product that Microsoft has ever produced. The amount of improvements across the platform really allows data professionals to have better and faster queries while simultaneously minimizing effort. One of the many things that it fixes is the ability to get an actual plan without having to re-run the query.

Currently, if you are not on SQL Server 2019 and wanted to see an execution plan, you would attempt to dive into the execution plan cache to retrieve an estimated plan. Keep in mind that this just an estimated plan and the actual plan, while the shape should be the same, will have runtime metrics. These actual runtime metrics could be, different than what is shown in the estimated plan, so it is important to get the actual whenever possible.
 
With the introduction of lightweight statistics, SQL Server can retain the metrics of the actual execution plan if enabled. Note that this could introduce a slight increase in overhead, however, I haven’t yet seen it be determinantal. These metrics are vastly important when performing query tuning.
 
Metrics
    Actual number of rows per operator
    Total CPU time
    Total execution time
    Actual maximum degree of parallelism
    Memory granted and subsequently used

Two new items are also introduced for this particular feature, one being a new database scoped configuration, LAST_QUERY_PLAN_STATS, and the other a new dynamic management function, sys.dm_exec_query_plan_stats.
 
LAST_QUERY_PLAN_STATS
Introduced in SQL Server 2016, database scoped configurations allow you to have more granular control of how the database is going to behave.
 
LAST_QUERY_PLAN_STATS can either be set globally with trace flag 2451 or on each individual database. It does require compatibility level of 150 so you must be on SQL Server 2019 to take advantage of this new ability. You can enable this database scoped configuration with this command.
    ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON 

Like with anything else, there is a minor overhead with enabling this. SQL Server does have to work just a tad bit harder to capture and store the runtime metrics for the plans. This overhead should be minimal but as always, proper testing should be completed against your given workloads.
 
SYS.DM_EXEC_QUERY_PLAN_STATS
Along with the new database scoped configuration comes a new dynamic management function, or DMF. This DMF requires the plan_handle, which is obtains by cross applying it with one of the other dmvs, such as,
    dm_exec_cached_plans
    dm_exec_query_stats
    dm_exec_requests
    dm_exec_procedure_stats
    dm_exec_trigger_stats

Here is an example query,
    SELECT * FROM SYS.DM_EXEC_CACHED_PLANS CP 
    CROSS APPLY SYS.DM_EXEC_QUERY_PLAN_STATS(CP.PLAN_HANDLE)  PS 
    WHERE PS.DBID = 10 

Let’s take a look at this feature in action.
 
Finding the Last Known Actual Plan
 Using WideWorldImporters, let’s first ensure that the database is at compatibility level of 150 and the new database scoped configuration is also enabled.

 
We can see that it is now at the correct compatibility level and the database scoped configuration is also been enabled. Now we can run a query and go find the actual plan utilizing the new DMF.
 
I am going to do a simple join between the Sales.Orders and Sales.OrderLines tables. After the results are returned, I’ll interrogate the new DMF using sys.dm_exec_cached_plans as a way to get the plan handle
    select * from sales.orders o 
           inner join sales.OrderLines ol on o.OrderID=ol.OrderID; 
    select size_in_bytes,cacheobjtype,objtype,plan_handle,ps.dbid, ps.query_plan 
    from sys.dm_exec_cached_plans cp 
           cross apply sys.dm_exec_query_plan_stats(cp.plan_handle) ps 
    where ps.dbid = 10 

Using LAST_QUERY_PLAN_STATS in SQL Server
 
The resulting query_plan column is being derived from the new DMF. If you click on the hyperlink in SQL Server Management Studio, it will open the query plan. Once opened, hit F4 or right click to show the properties of the plan and select the Clustered Index Scan of the Sales.OrderLines table.

You can now see the actual runtime statistics that SQL Server captured when the query was executed.
 
As I mentioned at the beginning of this post, SQL Server 2019 is one of the best releases of the product that Microsoft has ever accomplished. Features such as lightweight query profiling and LAST_QUERY_PLAN_STATS just continue to make the data professionals life that much easier.
 
Have you migrated to SQL Server 2019 yet? Are you using this feature? If you are, drop me a note! I’d love to hear about your experience with it!

SQL Server 2019 brings a very exciting new feature that was long overdue. Resumable Online Index Creation is one of my favorite new things. This, when paired with Resumable Index Rebuilds introduced with SQL Server 2017, really gives database administrators much more control over the index processes.

Have you ever started to build a new index on very large table only to have users call and complain their process is hung, not completing, or sthe ystem is slow? That’s when you realize you’re the cause because you tried to sneak in a new index. I have many times; because creating a new index can impact the performance and can be a problematic process for users when you have no or little downtime windows available. When you kill the create process, it rolls back requiring you to start from the beginning the next time. With resumable Online Index Creation, now you have the ability to pause and restart the build at the point it was paused. You can see where this can be very handy.

To use this option for creating the index, you must include "RESUMABLE=ON".

Let’s say you have only two 30-minute windows available to create this new index over the next two days. You could use the MAX_DURATION option with the new RESUMABLE=ON to specify the time interval for an index being built. Once the 30 minutes time is up, the index building automatically gets paused if it has not completed. When you’re ready the next day, you can RESUME right where it left off, allowing you to complete the process. Very cool.

Another added benefit is managing transaction log growth. As we all know, creating indexes, especially large ones, can cause hefty log growth events and can unfortunately lead to running out of disk space. This new functionality allows us to better manage that. We can now pause the process and truncate or backup the log mid process building the index in chunks.

In the case of when you create an index only to get complaints from users or manage your log growth, you can simply do the below to PAUSE and restart it when a time is better, or your transaction log maintenance has completed.

You can KILL the SPID creating the index or run the below.
ALTER INDEX MyResumableIndex ON MyTable PAUSE; 

To restart -

According to MSDN, Resumable online index create supports the follow scenarios:

• Resume an index creation operation after an index create failure, such as after a database failover or after running out of disk space.
• Pause an ongoing index creation operation and resume it later allowing to temporarily free system resources as required and resume this operation later.
• Create large indexes without using as much log space and a long-running transaction that blocks other maintenance activities and allows log truncation.

Note
SORT_IN_TEMPDB=ON is not supported when using RESUMABLE=ON

Once you pause it, how do you know how far the index got and how much is left to be created? With the Resumable REBUILD Index feature added in SQL Server 2017, we have also got a new sys.index_resumable_operations system view. This view shows us the percentage complete, current state, start time, and last pause time. I am very excited about this new Index Create feature. I think this is a big WIN for SQL Server 2019.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

There's so many new capabilities to play around and explore in SQL Server 2019. One of the new capabilities that has caught our attention is the introduction of JSON support. In this post, I will get started in creating a JSON document using SQL Server 2019.

SQL Server 2016 will support JSON (JavaScript Object Notation). JSON is an open, text-based exchange format based on JavaScript’s object literal notation. JSON is a popular data-interchange format used in modern web and mobile applications, as well for storing unstructured data. It is supported in several DB/NoSQL engines and this improvement will enable developers to put some JSON processing logic in the SQL Server that will enable them to parse, query, analyze, and update JSON data.

Step 1

In this post, you will query a standard table and then convert it into JSON format. For this example, you are querying the standard AdventureWorks database:

SELECT TOP(2) PP.[PersonType], PP.[FirstName], PP.[MiddleName], PP.[LastName], EA.[EmailAddress]
FROM Person.Person PP
INNER JOIN [Person].[EmailAddress] EA
ON EA.[BusinessEntityID] = PP.[BusinessEntityID]

Sample output for rows inside SQL Server Management Studio looks like below:

Step 2

Now, you will add the FOR JSON AUTO support to get the same output in JSON format.

SELECT TOP(2) PP.[PersonType], PP.[FirstName], PP.[MiddleName], PP.[LastName], EA.[EmailAddress]
FROM Person.Person PP
INNER JOIN [Person].[EmailAddress] EA
ON EA.[BusinessEntityID] = PP.[BusinessEntityID]
FOR JSON AUTO

The output looks like below:

[
{
"PersonType":"EM",
"FirstName":"X",
"MiddleName":"Y",
"LastName":"Z",
"EA":[{"EmailAddress":"[email protected]"}]
},
{"PersonType":"EM",
"FirstName":"A",
"MiddleName":"B",
"LastName":"C",
"EA":[{"EmailAddress":"[email protected]"}]
}
]

Step 3

You can also generate JSON using the PATH option like:

SELECT TOP(2) PP.[PersonType], PP.[FirstName], PP.[MiddleName], PP.[LastName], EA.[EmailAddress]
FROM Person.Person PP
INNER JOIN [Person].[EmailAddress] EA
ON EA.[BusinessEntityID] = PP.[BusinessEntityID]
FOR JSON PATH

The output for this query would look little different like:

[
{"PersonType":"EM", "FirstName":"X”, “MiddleName":"Y”, “LastName":"Z”, “EmailAddress":"[email protected]"},{"PersonType":"EM”, “FirstName":"A”, “MiddleName":"B”, “LastName":"C”, “EmailAddress":"[email protected]"}
]

Now that you are able to generate few simple output using JSON keyword.

HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

A sequence object in MS-SQL Server is designated to define and get only integer values, such as int, bigint, smallint, tinyint. However, if we want to generate sequence value(s) that are alpha-numeric, then we can define a Stored Procedure that can combine to generate an alpha-numeric combination of sequence values. This blog gives a complete idea of how this can be implemented.

CREATE DATABASE sampdb1

use sampdb1

--First Create a sequence object s3 which will generate numbers from 1 to 5 and cycles

CREATE SEQUENCE s3

START WITH 1

INCREMENT BY 1

MINVALUE 1

MAXVALUE 5

CYCLE

--Ensuring that the sequence object is defined properly and generating defined values.

SELECT NEXT VALUE FOR s3

--Defining a Stored Procedure that generates a Custom-sequence of values. This stored procedure is going to just get the generated alpha-numeric combination of the sequence.

CREATE PROCEDURE genSeqVals AS

Begin

DECLARE @n as int, @msg varchar(4)

SELECT @n=NEXT VALUE FOR s3

PRINT 'A'+cast(@n as varchar(2))

End

--Execute the following code and check.

Exec genSeqVals

--Defining another Stored Procedure that generates a Custom-sequence of values. This stored procedure returns the alpha-numeric combination of the sequence value that is generated using an OUTPUT parameter.

CREATE PROCEDURE getSeqVals(@res varchar(4) OUTPUT) AS

Begin

DECLARE @n as int, @msg varchar(4)

SELECT @n=NEXT VALUE FOR s3

SET @res = 'A'+cast(@n as varchar(2))

End

--Execute the following code and check.

DECLARE @seqnum varchar(4)

EXECUTE getSeqVals @seqnum output

print @seqnum

This is one way in which sequence objects can be custom-implemented as per the requirement. I hope that the above lines of code have given you deeper insight into T-SQL for custom implementation. Happy coding!

In this article, I am going to tell about the dropping of indexes in View in SQL Server. Views are virtual tables in which data from one or more tables gives the result set as our SQL table does with rows and columns. A View doesn’t store the data permanently in the database and at the time of execution, only its result set gets determined. When a View contains a large amount of rows and has complex logic in it then we can create an index on a View to improve the query performance. A View consists of a Unique Clustered Index and it is stored in the database as the clustered index does. Now, let’s run a few scenarios to check when Clustered index which is created on a View gets dropped automatically.

First, I will create a table on which I will run those scenarios.
CREATE TABLE[dbo]. [Customer] 
    ( 
        [CustomerID][int] IDENTITY(1, 1) NOT NULL, [CustomerName][varchar](50) NOT NULL, [CEO][varchar](40) NULL, [Phone][varchar](20) NOT NULL PRIMARY KEY CLUSTERED( 
            [CustomerID] ASC)) 
GO 

Now, I will create a View which will use this Customer table.
-- Create view 
Create VIEW vw_customer 
 WITH SCHEMABINDING  
AS  
 SELECT  CustomerID, CustomerName, CEO 
  from dbo.Customer 
  GO 

Here in the definition of View, I have used WITH SCHEMABINDING which is necessary for creating an index on a View. This option simulates that we cannot delete any of the base table used in the View and in order to make any changes, first, we need to drop or alter the View.

Also, all the table references in a View should have two part naming convention (schemaname.tablename) as we have in vw_Customer view (dbo.Customer).

Now, I will create an index on our View.

Scenario 1
Create index IX_CustomerID  
 ON vw_customer (CustomerID); 
 GO 

Error returned: Cannot create index on view 'vw_customer'. It does not have a unique clustered index. On Views, the first index must be a unique clustered index, so this will throw the error.

So, the first index on View should be UNIQUE CLUSTERD INDEX, else it will throw an error.

Scenario 2
Create unique clustered index IX_CustomerID  
 ON vw_customer (CustomerID) 
 GO 

Now, our Indexed View is created having a clustered index on it. Now, I want to alter my View and add one more column GETDATE() as CurrentDate  in the View definition and alter the View.

Now, alter the View after Scenario 2.

Scenario 3
ALTER  VIEW vw_customer 
 WITH SCHEMABINDING  
AS  
 SELECT  CustomerID, CustomerName , GETDATE() as CurrentDate 
  from dbo.Customer 
GO 

Now, as I have altered the View, I want to create another index on column CustomerName which will be a NonClustered index.

Scenario 4
Create index IX_CustomerName  
 ON vw_customer (CustomerName); 
GO 

Again, I get an error: Cannot create index on view 'vw_customer'. It does not have a unique clustered index.

As we have already created Unique Clustered Index on View, still it gives an error. The interesting thing is that after updating the View, the index that was created in Scenario 2 is dropped and the code will throw the same error as in Scenario 1.

As mentioned here,
"ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view."
Hope you will like this post.

In this blog, I will explain SQL Constraints. They are used to specify rules for the data in a table. The following SQL Constraints are commonly used (Primary key, foreign key, unique key, Composite key, null, Not Null, check).

Primary Key
The Primary key must contain be a unique value. It is the primary column and can’t have the null value. It uniquely identifies each row in a database table.
 
Syntax
    CREATE TABLE TB_NAME(Column1 datatype,column2 datatype PRIMARY KEY(Column1)) 

Foreign Key
1. Foreign key always refers to the primary key column.
2. Foreign key accepted to duplicate value.
 
Syntax
    CREATE TABLE TB_NAME(column1 datatype FOREIGN KEY REFERENCES(primary_key column_name),cloumn2 datatype) 

Unique Key
 
The unique key is the same as the primary key, but one row is accepted for the null value.
 
Syntax
    CREATE TABLE TB_NAME(Column_name datatatype UNIQUE,column_name2 datatype) 

Composite key
A composite key is a set of multiple keys that, together, uniquely identifies each record
 
Syntax
    CREATE TABLE TB_NAME(Column1 datatype,column2 datatype PRIMARY KEY(Column1,column2)) 

Not Null
Forces a column not to accept NULL values
 
Syntax
    CREATE TABLE TB_NAME(Column1 datatype,column2 datatype NOT NULL) 

Check
The CHECK constraint is used to limit the value range that can be placed in a column.
 
Syntax
    CREATE TABLE TB_NAME(MARKS INT CHECK(MARKS<=100))

Version control helps you to track the changes of a code repository. But, it doesn't much help to track database changes. General practice is to create a single script file that includes all the schema and update that file every time you make any changes into the database and commit it to version control. However, this is a bit longer a way to track the changes. Another way is to use popular tools like Red Gate Change Automation. But there is a native way around to handle tracking! simply put, DDL trigger can be used to track the DB changes.

Track Stored Procedure changes using DDL trigger

Here we'll see how to track stored procedure changes using DDL trigger.

Create your audit database and create a table.  
USE AuditDB;  
GO  
 
CREATE TABLE dbo.ProcedureChanges  
(  
    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  
    EventType    NVARCHAR(100),  
    EventDDL     NVARCHAR(MAX),  
    DatabaseName NVARCHAR(255),  
    SchemaName   NVARCHAR(255),  
    ObjectName   NVARCHAR(255),  
    HostName     NVARCHAR(255),  
    IPAddress    VARCHAR(32),  
    ProgramName  NVARCHAR(255),  
    LoginName    NVARCHAR(255)  
);   
Add data of all existing stored procedures from your actual database (Product DB in this example)
USE ProductDB;  
GO  
 
INSERT AuditDB.dbo.ProcedureChanges  
(  
    EventType,  
    EventDDL,  
    DatabaseName,  
    SchemaName,  
    ObjectName  
)  
SELECT  
    N'Initial control',  
    OBJECT_DEFINITION([object_id]),  
    DB_NAME(),  
    OBJECT_SCHEMA_NAME([object_id]),  
    OBJECT_NAME([object_id])  
FROM  
    sys.procedures;  
Create DDL trigger to capture changes
USE ProductDB;  
GO  
 
CREATE TRIGGER CaptureStoredProcedureChanges  
    ON DATABASE  
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,   
    ALTER_SCHEMA, RENAME  
AS  
BEGIN  
    SET NOCOUNT ON;  
 
    DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);  
 
    SELECT @ip = client_net_address  
        FROM sys.dm_exec_connections  
        WHERE session_id = @@SPID;  
 
    INSERT AuditDB.dbo.ProcedureChanges  
    (  
        EventType,  
        EventDDL,  
        SchemaName,  
        ObjectName,  
        DatabaseName,  
        HostName,  
        IPAddress,  
        ProgramName,  
        LoginName  
    )  
    SELECT  
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),   
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),  
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),   
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),  
        DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();  
END  
GO  

Modify any stored procedure and check the ProcedureChanges table from AuditDB.

The method might have some limitations, but this is the simplest way to tracking changes of small size databases.