The query to find who created or dropped or altered object in database – SQL

— read all available traces.

DECLARE @current VARCHAR(500);

DECLARE @start VARCHAR(500);

DECLARE @indx INT;

SELECT @current = path

FROM sys.traces

WHERE is_default = 1;

SET @current = REVERSE(@current)

SELECT @indx = PATINDEX(‘%\%’, @current)

SET @current = REVERSE(@current)

SET @start = LEFT(@current, LEN(@current) – @indx) + ‘\log.trc’;

— CHNAGE FILER AS NEEDED

SELECT CASE EventClass

WHEN 46

THEN ‘Object:Created’

WHEN 47

THEN ‘Object:Deleted’

WHEN 164

THEN ‘Object:Altered’

END

,DatabaseName

,ObjectName

,HostName

,ApplicationName

,LoginName

,StartTime

FROM::fn_trace_gettable(@start, DEFAULT)

WHERE EventClass IN (

46

,47

,164

)

AND EventSubclass = 0

AND DatabaseID <> 2

ORDER BY StartTime DESC

Reference:- http://goo.gl/JHLVLo

Happy Querying 🙂

Advertisements

Increment a value in the SET clause of an UPDATE Statement in SQL

I was working on a task in which I have to add one new column in my existing table and set its value given by the client which is incremental value to previous records.

For e.g.

Id         fname              lname               No

1          user 1              user 1              5

2          user 1              user 1              6

3          user 1              user 1              7

I was looking for some solution and ended up with following solution which works well for me.

-- Create Table
CREATE TABLE usermaster (
fname VARCHAR(20)
,lname VARCHAR(20)
)
-- Insert 100 Records
INSERT usermaster
VALUES (
'user fname'
,'user lname'
) GO 100 -- You can specify any no. of records
-- Add one more column called id using ALTER statement
ALTER TABLE usermaster ADD id INT
-- Update Id column with Increment value
DECLARE @id INT
SET @id = 0 -- you can specify any no. I set it 0 so it start with 1.
UPDATE usermaster
SET @id = id = @id + 1
GO
-- check updated table records
SELECT *
FROM usermaster
GO

Happy Querying 🙂

Microsoft Introduce ASP.Net WebHooks

Microsoft Introduce ASP.Net WebHooks. In the MSDN blog article you will get an overview of how to work with Microsoft ASP.NET WebHooks. Microsoft ASP.Net Webhooks make it easy to send and receive webhooks from web application.

MSDN Blog:

Introducing Microsoft ASP.NET WebHooks Preview

Sending WebHooks with ASP.NET WebHooks Preview

Happy Coding 🙂