Find Members of an NT Group

-- Find Members of DOMAIN belonging to a user group 'UserGroup'

exec master..xp_logininfo 'DOMAIN\UserGroup','members'
exec master..xp_logininfo 'DOMAIN\Administrators','members'

user connections ,Logical Connections, Threads, Logins , Logouts

select *
from sys.dm_os_performance_counters a
where a.object_name = 'SQLServer:General Statistics'
and counter_name in ('user connections ','Logical Connections','Logins/sec','Logouts/sec')

--Number of threads used with MSSQL 2005

select count(*) as 'Number of threads'
from sys.dm_os_waiting_tasks
where wait_type <> 'THREADPOOL';

Statements having High CPU usage on execution count

-- High CPU queries -- on execution count SELECT TOP 50 total_worker_time/execution_count AS avg_cpu_cost, execution_count,db_name(depamain.dbid) as dbname,object_name(depamain.objectid,depamain.dbid) as objname, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats OUTER APPLY ( select dbid,objectid from ( select attribute,cast(value as int) as attvalue from sys.dm_exec_plan_attributes(plan_handle) where attribute in ('dbid','objectid') ) as depa PIVOT ( MAX(depa.attvalue) FOR depa.attribute IN ("dbid", "objectid") ) AS depapvt ) as depamain ORDER BY execution_count DESC;

Statements having High CPU Usage

select top 50 total_worker_time/execution_count as avg_cpu_cost, execution_count,db_name(depamain.dbid) as dbname,object_name(depamain.objectid,depamain.dbid) as objname,
(selecct substriung(text, statement_start_offset/2 + 1,
(case when statement_end_offset = -1 then len(covert(nvarchar(max), text)) * 2 else statement_end_offset nd - statement_start_offset)/2)
from sys.dm_exec_sql_text(sql_handle)) as query_text
from sys.dm_exec_query_stats
outer apply
( select dbid,objectid from
( select attribute,cast(value as int) as attvalue
from sys.dm_exec_plan_attributes(plan_handle)
where attribute in ('dbid','objectid')
) as depa
pivot ( max(depa.attvalue) for depa.attribute
in ("dbid", "objectid") ) as depapvt ) as depamain
where execution_count > 4
order by [avg_cpu_cost] desc;

Drop column after checking regular constraints

-- use database
go
-- SQL 2005 and above
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_lib_drop_column]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_lib_drop_column]
GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
 /* exec sp_lib_drop_column 'constratintcheckhead','accountid','l' exec sp_lib_drop_column 'constratintcheckhead','accountid','d'
-- testing
create table constratintcheckhead ( accountid int not null primary key ,clientid int not null ) create table constraintcheckdetails ( accountid int not null ,notesid int not null primary key (accountid,notesid) )
alter table constratintcheckhead add constraint DF_accountid default 0 for accountid
alter table constratintcheckhead add constraint CK_accountid check (accountid >= 0 )
alter table constratintcheckhead add constraint UQ_account Unique (accountid,clientid ) alter table constraintcheckdetails add constraint FK_constraintcheckdetails_constratintcheckhead foreign key (accountid) references constratintcheckhead (accountid)
create index IX_constratintcheckhead_clientid on constratintcheckhead(clientid) with fillfactor = 90
create index IX_constratintcheckhead_clientid_accountid on constratintcheckhead(clientid,accountid) with fillfactor = 90
drop table constratintcheckhead
drop table constraintcheckdetails
Description :
Lists/Drops Constartints and indexes for a column and drops the column Following constraints are checked only Default,Check, Foreign Key, Primary Key,Unique Key Indexes : Clustered or Non Clustered This will not check column is replicated or part of full text etc Only SQL 2005 and above WARNING: Once constaints are dropped , column will be dropped Assumes there are not more than 10 columns in an index

Usage : exec sp_lib_drop_column 'constratintcheckhead','accountid','l'
exec sp_lib_drop_column 'constratintcheckhead','accountid','d'
Input Parameters : @tablename , @columnname ,@flag char(1) @flag -- 'l' -- list constraints only , 'd' -- drop column , 'b' -- list constraints and drop column
Output Parameters : None
Return Value : None
Record Set : None
 */

create procedure dbo.sp_lib_drop_column @tablename varchar(150), @columnname varchar(80) ,@flag char(1) = 'l'
as
set nocount on

if @flag not in ('l','d','b')
return
declare @slno int ,@maxslno int,@vchsql varchar(4000),@ci_name varchar(150),@ci_type char(1)
create table #temp_constraints( tablename varchar(150),type_desc varchar(150),ci_name varchar(150),columnname varchar(80),ci_type char(1), parentablename varchar(150),parentcolumname varchar(80),slno int identity(1,1) primary key)
declare @numbers table ( n int primary key )
insert into @numbers values (1)
insert into @numbers values (2)
insert into @numbers values (3)
insert into @numbers values (4) insert
into @numbers values (5)
insert into @numbers values (6)
insert into @numbers values (7)
insert into @numbers values (8)
insert into @numbers values (9)
insert into @numbers values (10)

 insert into #temp_constraints (tablename,type_desc,ci_name,columnname,ci_type,parentablename,parentcolumname )select object_name(parent_object_id) as tablename,type_desc,name as ci_name,
col_name(parent_object_id,parent_column_id) as columnname,'N' as ci_type ,'' as parentablename,'' as parentcolumname
from sys.default_constraints
where object_name(parent_object_id) = @tablename
and col_name(parent_object_id,parent_column_id) = @columnname
union all
select object_name(parent_object_id) as tablename,type_desc,name as ci_name,col_name(parent_object_id,parent_column_id) as columnname, 'N' as ci_type ,'' as parentablename,'' as parentcolumname
from sys.check_constraints
where object_name(parent_object_id) = @tablename and col_name(parent_object_id,parent_column_id) = @columnname
union all
select object_name( parent_object_id) as tablename, 'FOREIGN KEY' as type_desc, object_name(constraint_object_id) as ci_name,col_name(parent_object_id,parent_column_id) as columnname,'N' as ci_type,object_name( referenced_object_id) as parentablename, col_name(referenced_object_id,referenced_column_id) as parentcolumname
from sys.foreign_key_columns
where object_name( referenced_object_id) = @tablename
and col_name(referenced_object_id,referenced_column_id) = @columnname
union all
select object_name(i.object_id) as tablename, case when o.type = 'PK' then 'PRIMARY KEY' when o.type = 'UQ' then 'UNIQUE'else 'INDEX ' end as type_desc, i.name as ci_name,t.index_column_name as columnname, case when o.type in ('PK','UQ') then 'N' else 'I' end as ci_type,'' as parentablename, '' as parentcolumname
from sys.indexes i
left outer join sys.objects o
on o.parent_object_id = i.object_id
and i.name = o.name
cross apply
( select index_col(object_name(i.object_id),index_id,n) as index_column_name from @numbers where index_col(object_name(i.object_id),index_id,n) = @columnname ) t where object_name(i.object_id) = @tablename
----- if @flag in ('l','b')
select * from #temp_constraints order by slno
 if @flag = 'l'
return
set @slno = 0
select @maxslno = max(slno) from #temp_constraints
while @slno < @maxslno
begin 
    set @slno = @slno + 1
    select @tablename = tablename, @ci_name = ci_name, @ci_type = ci_type
    from   #temp_constraints where slno = @slno
   if @ci_type = 'N' set @vchsql = 'alter table ' + @tablename + ' drop constraint '   + @ci_name else if @ci_type = 'I' set @vchsql = 'drop index ' + @tablename + '.' + @ci_name
-- print @vchsql
 exec (@vchsql)
end
drop table #temp_constraints
return