Information related to the security admin server role and system admin server role

Security is a very important aspect that a DBA should know about. It is also important to know which login has a sysadmin or security admin server level role. The following SQL Command will show information related to the security admin server role and system admin server role.

SELECT l.name, l.denylogin, l.isntname, l.isntgroup, l.isntuser
FROM master.dbo.syslogins l
WHERE l.sysadmin = 1 OR l.securityadmin = 1

Server level Configuration Information

Server level configuration controls some of the features and performance of SQL Server. It is also important for a SQL Server DBA to know the server level configuration information. The following SQL Statement will give all of the information related to Server level configuration.

SELECT * from sys.configurations order by NAMEIf you are using SQL Server 2000, you can execute the following command instead.

SP_CONFIGURE 'show advanced options',1
go
RECONFIGURE with OVERRIDE
go
SP_CONFIGURE
go

SERVERPROPERTY

The following T-SQL statement retrieves information such as Hostname, Current instance name, Edition, Server type, ServicePack and version number from current SQL Server connection. 'Edition' will give information on a 32 bit or 64 bit architecture and 'Productlevel' gives information about what service pack your SQL Server is on. It also displays if the current SQL Server is a clustered server.

SELECT
SERVERPROPERTY('MachineName') as Host,
SERVERPROPERTY('InstanceName') as Instance,
SERVERPROPERTY('Edition') as Edition, /*shows 32 bit or 64 bit*/
SERVERPROPERTY('ProductLevel') as ProductLevel, /* RTM or SP1 etc*/
Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else
'STANDALONE' end as ServerType,
@@VERSION as VersionNumber

Kill ALL Connections To a SQL Database

The Kill Connection script utilizes sys.dm_tran_locks (syslockinfo for SQL 2000) to capture SPIDs that are accessing the specified database. The traditional way of using sysprocesses table alone can not detect distributed transactions or cross-database references to the database.


Create Proc [dbo].[usp_killConnections]
@db_name Nvarchar(200)
AS
set nocount on
if db_id(@db_name) is null
return

declare @spid int
declare spid cursor for

/* For SQL 2000 and SQL 2005/2008 Backward compatible mode*/

select spid from master.dbo.sysprocesses(nolock) where dbid = db_id(@db_name) and spid > 50
union
select distinct req_spid from sys.syslockinfo(nolock) where rsc_dbid = db_id(@db_name) and req_spid > 50

/* For SQL 2005/2008 */
/*
select spid from master.dbo.sysprocesses(nolock) where dbid = db_id(@db_name) and spid > 50
union
select distinct request_session_id from sys.dm_tran_locks (nolock) where resource_database_id = db_id(@db_name) and request_session_id > 50
*/

open spid
fetch next from spid
into @spid

while @@fetch_status = 0
begin
exec ('kill ' + @spid)
fetch next from spid into @spid
end

close spid
deallocate spid

String tokenizing / splitting

Simply copy and paste the Split() function code in your T-SQL code editor, to select appropriate DB from database combo box and then press F5 key to create user defined Split() function.

Now you can use it as follows:
1. The first parameter of function is string that you need to split.
2. And second parameter of function is delimiter.

SELECT * FROM dbo.Split ('Token1;Token2;Token3;Token4;Token5',';')

The Expected output will be as follow:
Token1
Token2
Token3
Token4
Token5



CREATE FUNCTION dbo.Split
(
@String VARCHAR(8000),
@Delimiter NVARCHAR(1)
)
RETURNS @Tokens table (Token NVARCHAR(255))
AS
BEGIN
WHILE (CHARINDEX(@Delimiter,@String)>0)
BEGIN
INSERT INTO @Tokens (Token) VALUES
(LTRIM(RTRIM(SUBSTRING(@String,1,CHARINDEX(@Delimiter,@String)-1))))
SET @String = SUBSTRING(@String, CHARINDEX(@Delimiter,@String)+LEN(@Delimiter),LEN(@String))
END
INSERT INTO @Tokens (Token) VALUES (LTRIM(RTRIM(@String)))
RETURN
END

Unused indexes in your databases

This Script allows you to determine the list of unused indexes in your databases


select object_name (i.object_id) as NomTable,isnull( i.name,'HEAP') as IndexName
from sys.objects o inner join sys.indexes i
ON i.[object_id] = o.[object_id] left join
sys.dm_db_index_usage_stats s
on i.index_id = s.index_id and s.object_id = i.object_id
where object_name (o.object_id) is not null
and object_name (s.object_id) is null
AND o.[type] = 'U'
and isnull( i.name,'HEAP') <>'HEAP'

union all

select object_name (i.object_id) as NomTable,isnull( i.name,'HEAP') as IndexName
from sys.objects o inner join sys.indexes i
ON i.[object_id] = o.[object_id] left join
sys.dm_db_index_usage_stats s
on i.index_id = s.index_id and s.object_id = i.object_id
where user_seeks= 0 and user_scans=0 and user_lookups= 0 AND o.[type] = 'U'
and isnull( i.name,'HEAP') <>'HEAP'
order by NomTable asc

order by OBJECT_NAME(i.object_id) ASC

select type,* from sys.objects
where name = 'DDAOeuvre'
where user_seeks=0 and user_scans=0 and user_lookups = 0
order by object_name (s.object_id) asc

ROW COUNT

SELECT o.name AS "Table Name", i.rowcnt AS "Row Count"
FROM sysobjects o, sysindexes i
WHERE i.id = o.id AND indid IN(0,1)
AND xtype = 'u'
AND o.name <> 'sysdiagrams'
ORDER BY i.rowcnt DESC

Searching through SPs, Tables, Views

This little procedure is very useful during development / support to quickly find all objects that may have something to do with a certain term, column name, part of thereof and so on.

CREATE PROCEDURE adhoc_SearchText(@text VARCHAR(1024))
AS
BEGIN
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES T
WHERE charindex(@text, T.TABLE_NAME)>0

SELECT C.TABLE_NAME, C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE charindex(@text, C.COLUMN_NAME)>0

SELECT V.TABLE_NAME AS VIEW_NAME
FROM information_schema.VIEWS V
WHERE charindex(@text, V.VIEW_DEFINITION)>0

SELECT R.ROUTINE_NAME
FROM information_schema.routines r
WHERE charindex(@text, r.ROUTINE_DEFINITION)>0
END

Get the Table definition from Database

Table definition from SQL server tables

sp_help --(followed by a table name)

ex: sp_help EMP

Table definition from Oracle tables

desc --(followed by a table name)

ex: desc EMP