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