Trigger for new Database

I got a mail asking for trigger on new Database

Hi.
I need to implement a trigger to email me when someone creates a database on a server without letting me know.I dont know how to write the trigger.Can you help please?
Thanks
Rezwan

Ans:-
you have to use ddl trigger which controls different activity on the server

Here is example for trigger on create database statement

CREATE TRIGGER MONITOR_DATABASE
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
DECLARE @CREATE_DB_STATEMENT VARCHAR(MAX)

SELECT @CREATE_DB_STATEMENT =
EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’)

SELECT @CREATE_DB_STATEMENT
/* use sp_send_dbmail statement here */
END

you have to use sp_send_dbmail in trigger to send mail and use
@CREATE_DB_STATEMENT as message for mail .

Cheers
Amish Shah

How to find Index information – sp_msindexspace,sp_helpindex,sp_mshelpindex

Index are very important part for any database.
We need to regularly monitor index and maintain index for better performance of the system.
I have already written an article on how to find highly used index

Today I am writing on how to get basic information related to index

Lets create a table

create table index_test
(id int,
data1 varchar(100),
data2 varchar(200))

Now we will create a clustered index

create clustered index idx_clust_index_test_id on index_test(id)

Now we will create a non clustered index.SQL 2005 is also supporting included column.
we will create index with included column

create index idx_index_test_data1 on index_test(data1)
include (data2)

Now if we want to get info for indexes for this table.
Here are various option available to get all this different information

sp_helpindex index_test
This will provide basic info for the index

Result

index_name index_description index_keys

idx_clust_index_test_id clustered located on PRIMARY id
idx_index_test_data1 nonclustered located on PRIMARY data1

Below two are undocumented stroe proc.

sp_mshelpindex index_test
This will provide basic info along with index_id, fillfactor and status of fulltextkey , computed , IsTable

Result is large so not published here

sp_msindexspace index_test

Index ID Index Name Size (KB) Comments
1 idx_clust_index_test_id 16 Size excludes actual data.
2 idx_index_test_data1 40 (None)

Now if want to look at system tables for index infomation

select object_name(object_id) object, * from sys.indexes where object_id = object_id (‘index_test’)

select object_name(c.object_id) object,i.name index_name,i.index_id,cl.name column_name, c.index_column_id,c.key_ordinal,c.partition_ordinal,c.is_descending_key,c.is_included_column from sys.index_columns c inner join sys.indexes i on c.object_id = i.object_id and c.index_id = i.index_id inner join sys.columns cl on c.object_id = cl.object_id and c.column_id = cl.column_id
where c.object_id = object_id (‘index_test’)

Remember on idx_index_test_data1 we have one included column data2. no system proc will give info for included column but above query will give infor included column also. sys.index_column has one column is_included_column give us info either the column is included.

sp_changesubscription for changing subscriber password

For DBA its a routine activity to change server passwords.
If we are using replication between servers and if we are changing password of subscribers
we have to change it on publisher also.

we can use sp_changesubscription here to change password for the subscriber.

We can use it for changes the properties of a snapshot or transactional push subscription or a pull subscription involved in queued updating transactional replication

We have to run this on publication database

EXEC Sp_changesubscription

    @publication =‘test’,

    @article = ‘all’,— here password change will affect all articles

    @destination_db =‘dest-test’,

    @subscriber =‘REPORTSERVER’,

    @property =‘subscriber_password’,

    @value = ‘abc123’

Also it support many other properties for subscribers which we can change.

for other replication we have to use sp_change_subscription_properties (Transact-SQL).

This also support changes of password like

distrib_job_password,distributor_password,dts_package_password ,ftp_password ,internet_password,merge_job_password

Look at BOL for more detail information .