List Indexes and their size

SELECT OBJECT_NAME(i.OBJECT_ID)AS TableName,

i.name AS IndexName, i.index_id AS IndexID,

(8 *SUM(a.used_pages))/1024 AS 'Indexsize(MB)'

FROM sys.indexes AS i

JOIN sys.partitions AS p ON p.OBJECT_ID= i.OBJECT_ID AND p.index_id = i.index_id

JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

GROUP BY i.OBJECT_ID, i.index_id,i.name

ORDER BY OBJECT_NAME(i.OBJECT_ID), i.index_id

MvM

List all indexes & their included columns in a database

SELECT '['+Sch.name+'].['+ Tab.[name]+']' AS TableName,

Ind.[name] AS IndexName,

SUBSTRING ((SELECT', '+ AC.name

FROM sys.[tables] AS T

INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]

INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]

AND I.[index_id] = IC.[index_id]

INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]

AND IC.[column_id] = AC.[column_id]

WHERE Ind.[object_id] = I.[object_id]

AND Ind.index_id = I.index_id AND IC.is_included_column = 0

ORDER BY IC.key_ordinal

FOR XML PATH('')), 2, 8000) AS KeyCols,

SUBSTRING ((SELECT', '+ AC.name

FROM sys.[tables] AS T

INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]

INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]

AND I.[index_id] = IC.[index_id]

INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]

AND IC.[column_id] = AC.[column_id]

WHERE Ind.[object_id] = I.[object_id] AND Ind.index_id = I.index_id

AND IC.is_included_column = 1

ORDER BY IC.key_ordinal

FOR XML PATH('')), 2, 8000) AS IncludeCols

FROM sys.[indexes] Ind

INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]

INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]

ORDER BY TableName

MvM

List larger tables in a database

I have tried this query only on sql 2008. This will extract all tables with number of rows, reserved,data,index and unused space in MB.

select

name =object_schema_name(object_id)+'.'+object_name(object_id),

rows =sum(case when index_id < 2 then row_count else 0 end),

reserved_MB = 8*sum(reserved_page_count)/1024,

data_MB = 8*sum(case when index_id<2

then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count

else lob_used_page_count + row_overflow_used_page_count end)/1024,

index_MB = 8*(sum(used_page_count)-sum(case when index_id<2

then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count

else lob_used_page_count + row_overflow_used_page_count end))/1024,

unused_kb = 8*sum(reserved_page_count-used_page_count)

from sys.dm_db_partition_stats

where object_id> 1024

group by object_id

order by rows desc;

MvM

List Recovery Model for all databases in SQL Server 2000

Two ways I used to find it. But the best one is the first one listed.

SELECT Name,

DATABASEPROPERTYEX (Name,'RECOVERY') AS [Recovery Model]

FROM master.dbo.sysdatabases

OR

sp_msforeachdb

'use [?] select ''?'' as DatabaseName, DATABASEPROPERTYEX(N''?'', N''RECOVERY'')'

MvM

Query to find Database Details in SQL Server 2000

Here's the SQL Server 2000 version of the query.
Add columns as per your requirement.

select Name, SUSER_SNAME(owner_sid) as Owner,

[Total Size MB], cmptlevel

from sysdatabases MST inner join (select b.name [LOG_DBNAME],

CONVERT(DECIMAL(10,2), sum(CONVERT(DECIMAL(10,2),(a.size * 8))/1024))

as [Total Size MB] from sysaltfiles A

inner join sysdatabases B on A.dbid= B.dbid

group by b.name)AA on AA.[LOG_DBNAME] = MST.name

order by owner;

MvM

Here's the SQL Server 2000 version of the query.
Add columns as per your requirement.

select Name, SUSER_SNAME(owner_sid) as Owner,

[Total Size MB], cmptlevel

from sysdatabases MST inner join (select b.name [LOG_DBNAME],

CONVERT(DECIMAL(10,2), sum(CONVERT(DECIMAL(10,2),(a.size * 8))/1024))

as [Total Size MB] from sysaltfiles A

inner join sysdatabases B on A.dbid= B.dbid

group by b.name)AA on AA.[LOG_DBNAME] = MST.name

order by owner;

MvM

Query to find Database Details in SQL Server 2005 & 2008

Here's the query which I am using.

select Name, SUSER_SNAME(owner_sid) as Owner, create_date,

state_desc, [Total Size MB], recovery_model_desc,

compatibility_level, collation_name, user_access_desc

from sys.databases MST

inner join (select b.name [LOG_DBNAME],

CONVERT(DECIMAL(10,2), sum(CONVERT(DECIMAL(10,2), (a.size * 8))/1024)) [Total Size MB]

from sys.sysaltfiles A

inner join sys.databases B on A.dbid= B.database_id

group by b.name) AA on AA.[LOG_DBNAME] = MST.name

order by owner

MvM

Ctrl Alt Del in Remote Desktop

I have had this situation so many times that I wanted to press ctrl+alt+del in my remote desktop window and I did and then the local computer responds.
Then, how to do it? Here’s the wonder…Its…

CTRL + ALT + END

wow…that sounds bad…but yes, thats the way out…

MvM

[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’.

I have seen this error lot of times in SQL Server 2000 Maintenance Plans. When job fails, it doesn’t specifically say anything in the ‘Job History’. So, what I used to do is to run the Optimisation code (copied from the job) in Query Analyzer. This will list out each step result for the plan. Thats how I used to see this error message. This specifically appears for the ‘Integrity Checks’ and ‘Optimisation’ Job Plans.

Here’s how to resolve it.

Optimizations Job for DB Maintenance Plan ‘DB Maintenance Plan name‘For example, add the parameters to the command as shown in the following command:
EXECUTE master.dbo.xp_sqlmaint N’-S ServerName\InstanceName -PlanID -WriteHistory -RebldIdx 10 -SupportComputedColumn’

Integrity Checks Job for DB Maintenance Plan ‘DB Maintenance Plan name‘For example, add the parameters to the command as shown in the following command:
EXECUTE master.dbo.xp_sqlmaint N’-S ServerName\InstanceName -PlanID -WriteHistory -CkDB -SupportComputedColumn’

More details and explanations can be found in http://support.microsoft.com/kb/902388

MvM

Change Maintenance Plan Owner to ‘sa’

I always had this annoying issue where in I had to update the job owner to ‘sa’ once someone had modified a maintenance plan. This happens when a user logs in with his id (which usually everyone does) and modifies a Maintenance Plan and then saves it. This automatically changes the owner of the job corresponding to it.

Below mentioned query is handy when you need to update lot of Maintenance Plans like this.

–SQL 2005

UPDATE msdb.dbo.sysdtspackages90

SET ownersid =SUSER_SID(‘sa’)

WHERE name = ‘MAINT’

 –SQL 2008

UPDATE msdb.dbo.sysssispackages

SET ownersid =SUSER_SID(‘sa’)

WHERE name = ‘MAINT’

–**************************

MvM

Database, Users and their DB Roles (SQL Server 2000 & 2005)

SQL SERVER 2005

DECLARE @db_name SYSNAME, @sql VARCHAR(1000)

DECLARE db_cursor CURSOR FOR

SELECT Name FROM sys.databases

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @db_name

WHILE (@@FETCH_STATUS= 0)

BEGIN

SET @sql =‘SELECT ”’+ @db_name + ”’ AS [Database],

USER_NAME(role_principal_id) AS [Role], USER_NAME(member_principal_id) AS [User]

FROM ‘+ @db_name +‘.sys.database_role_members’

EXEC(@sql)

 FETCH NEXT FROM db_cursor INTO @db_name

END

CLOSE db_cursor

DEALLOCATE db_cursor

SQL SERVER 2000

DECLARE @db_name SYSNAME, @sql VARCHAR(1000)

 DECLARE db_cursor CURSOR FOR

SELECT Name FROM master..sysdatabases

 OPEN db_cursor FETCH NEXT

FROM db_cursor INTO @db_name

WHILE (@@FETCH_STATUS= 0)

 BEGIN

SET @sql =‘SELECT ”’+ @db_name + ”’ AS [Database],

USER_NAME(memberuid) AS [Role], USER_NAME(groupuid) AS [User] 

FROM sysmembers’

EXEC (@sql)

FETCH NEXT FROM db_cursor INTO @db_name

END

CLOSE db_cursor

DEALLOCATE db_cursor;

–====================================

MvM

Query to find Users and their Database Roles in SQL Server 2000

SELECT USER_NAME(memberuid) as UserName,

USER_NAME(groupuid) as Role

FROM sysmembers ;

MvM

Query to find Server Roles and its members in SQL Server 2000

select ‘ServerRole’= spv.name, ‘MemberName’= lgn.name

from master.dbo.spt_values spv,

master.dbo.sysxlogins lgn

where spv.low = 0 and spv.type=‘SRV’

and lgn.srvid IS NULL and spv.number & lgn.xstatus = spv.number;

MvM

Stored Procedure to list Users & their Database Roles

USE [msdb]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[List_DBRoles]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[List_DBRoles]
GO

CREATE procedure [dbo].[List_DBRoles]
( @database nvarchar(128)=null, @user varchar(20)=null,
@dbo char(1)=null, @access char(1)=null,
@security char(1)=null, @ddl char(1)=null,
@datareader char(1)=null, @datawriter char(1)=null,
@denyread char(1)=null, @denywrite char(1)=null
) as

declare @dbname varchar(200)
declare @mSql1 varchar(8000)

CREATE TABLE #DBROLES
(DBName sysname not null, UserName sysname not null,
db_owner varchar(3) not null, db_accessadmin varchar(3) not null,
db_securityadmin varchar(3) not null, db_ddladmin varchar(3) not null,
db_datareader varchar(3) not null, db_datawriter varchar(3) not null,
db_denydatareader varchar(3) not null, db_denydatawriter varchar(3) not null
)

DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases 
where name not in ('mssecurity','tempdb')
order by name

OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN

Set @mSQL1 = ' Insert into #DBROLES ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter, db_denydatareader, db_denydatawriter )
SELECT  '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+'
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from [' + @dbName+'].dbo.sysmembers a '+char(13)+
'join ['+ @dbName+'].dbo.sysusers b '+char(13)+
'on a.memberuid = b.uid join ['+@dbName +'].dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'

Execute (@mSql1)

FETCH NEXT FROM DBName_Cursor INTO @dbname
END
CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor

Select * from #DBRoles
where ((@database is null) OR (DBName LIKE '%'+@database+'%')) AND
((@user is null) OR (UserName LIKE '%'+@user+'%')) AND
((@dbo is null) OR (db_owner = 'Yes')) AND
((@access is null) OR (db_accessadmin = 'Yes')) AND
((@security is null) OR (db_securityadmin = 'Yes')) AND
((@ddl is null) OR (db_ddladmin = 'Yes')) AND
((@datareader is null) OR (db_datareader = 'Yes')) AND
((@datawriter is null) OR (db_datawriter = 'Yes')) AND
((@denyread is null) OR (db_denydatareader = 'Yes')) AND
((@denywrite is null) OR (db_denydatawriter = 'Yes'))

drop table #DBRoles
------
MvM

Query to find Database Roles & its associated members

USE master

DECLARE @name VARCHAR(50) -- database name 
CREATE TABLE #temprr(RoleName nvarchar(max),MemberName nvarchar(max), LoginType nvarchar(max))
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 
WHILE @@FETCH_STATUS= 0 
BEGIN
PRINT @name
INSERT INTO #temprr
EXEC('USE ['+ @name +']; 
select rp.name as RoleName, mp.name as MemberName, mp.type_desc as Type from sys.database_role_members drm join sys.database_principals rp on (drm.role_principal_id = rp.principal_id) join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)')
FETCH NEXT FROM db_cursor INTO @name 
END
CLOSE db_cursor 
DEALLOCATE db_cursor
SELECT * FROM #temprr
where MemberName notlike'NT%'and MemberName notlike'##%'

order by MemberName
drop table #temprr;

MvM