SQL SERVER – 2005 – List Tables in Database With/Without Primary Key

USE DatabaseName;
GO
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;
GO

SELECT i.name AS IndexName,OBJECT_NAME(ic.OBJECT_ID) AS TableName,COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

Add windows user to sysadmin role to SQL server 2005

A SQL Server instance can contain multiple user databases. Each user database has a specific owner; the owner defaults to the database creator. By definition, members of the sysadmin server role (including system administrators if they have access to SQL Server through their default group account) are database owners (DBOs) in every user database. In addition, there is a database role, db_owner, in every user database. Members of the db_owner role have approximately the same privileges as the dbo user.

To explicitly grant SQL Server 2005 logins directly to the service accounts that are used by SQL Server 2005 and by SQL Server Agent.
CREATE LOGIN [FFSERVER\Calldp] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

Use an account that is a member of the SYSADMIN fixed server role to provision the logins that you added in above step in the SYSADMIN fixed server role.
EXEC master..sp_addsrvrolemember @loginame = N'FFSERVER\Calldp', @rolename = N'sysadmin'

To get table and column which are using particular data type

select object_name(c.id),c.name
from sys.objects o,
sys.syscolumns c,
sys.types t
where o.object_id=c.id
and c.xtype=t.system_type_id
and t.name='varbinary'
and o.type='U'

Some usefull queries in performance monitoring

Some usefull queries in performance monitoring:
SELECT
d1.session_id,
d3.[text],
d1.login_time,
d1.login_name,
d2.wait_time,
d2.blocking_session_id,
d2.cpu_time,
d1.memory_usage,
d2.total_elapsed_time,
d2.reads,d2.writes,
d2.logical_reads,
d2.sql_handle
FROM sys.dm_exec_sessions d1
JOIN sys.dm_exec_requests d2 ON d1.session_id=d2.session_id
CROSS APPLY sys.dm_exec_sql_text(d2.sql_handle) d3

Idntify locking query:
select distinct
t1.session_id as THIS_IS_Blocked_session,
t1.status as Blocked_session_status,
t1.command as Blocked_session_command,
t1.DatabaseName,
t1.text as Blocked_session_text,
t2.session_id as BY_THIS_Blocking_session,
t2.status as Blocking_session_status,
t2.command as Blocking_session_command,t2.DatabaseName,t2.text as Blocking_session_text
from
(
select
er.session_id,
er.status,
er.command,
DB_NAME(database_id) AS 'DatabaseName',
SUBSTRING(st.text, (er.statement_start_offset/2)+1,((CASE er.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE er.statement_end_offset END - er.statement_start_offset)/2) + 1) AS text
FROM
sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
) as T1
join sysprocesses as s on t1.session_id = s.spid
join syslockinfo as l on s.spid = l.req_spid
join
(
select
er.session_id,
er.status,
er.command,
DB_NAME(database_id) AS 'DatabaseName',
SUBSTRING(st.text, (er.statement_start_offset/2)+1,((CASE er.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE er.statement_end_offset END - er.statement_start_offset)/2) + 1) AS text
FROM
sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
) as T2 on s.blocked = t2.session_id
where s.blocked >0

Script for Triggers in SQL server

-- To drop triggers associated with Particular table

select object_name(parent_obj),' drop trigger '+object_name(b.id)--b.text
from sysobjects a
join syscomments b
on a.id=b.id
where object_name(parent_obj)='Table_Name'
and xtype='TR'



-- To create script of triggers associated with Particular table
select object_name(parent_obj),object_name(b.id),b.text
from sysobjects a
join syscomments b
on a.id=b.id
where object_name(parent_obj)='Table_Name'
and xtype='TR'