Thứ Ba, 18 tháng 4, 2017

[SQL] Checking ANSI_NULLS and QUOTED_IDENTIFIER Options

SELECT o.name, o.xtype, m.definition, m.uses_ansi_nulls, m.uses_quoted_identifier
FROM sys.sql_modules  m  
INNER JOIN sysobjects o ON m.object_id = o.id 

Thứ Năm, 23 tháng 3, 2017

Rebuild DB Indexes

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR

SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM information_schema.tables
WHERE TABLE_TYPE = 'base table'
and TABLE_NAME in (
SELECT distinct OBJECT_NAME(a.object_id)
FROM sys.dm_db_index_physical_stats (DB_ID(),null, NULL, NULL, NULL) AS a  
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where avg_fragmentation_in_percent > 30
)
 
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

Print @TableName
DBCC DBREINDEX(@TableName,' ',90)

FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor


https://msdn.microsoft.com/en-us/library/ms181671.aspx
https://msdn.microsoft.com/en-us/library/ms177459.aspx
https://msdn.microsoft.com/en-us/library/ms189858.aspx
https://gallery.technet.microsoft.com/scriptcenter/Script-for-rebuilding-all-8d079754

Thứ Năm, 16 tháng 3, 2017

Check DB Index Fragmentation

SELECT a.index_id
,name
,avg_fragmentation_in_percent
,OBJECT_NAME(a.object_id) as TableName
FROM sys.dm_db_index_physical_stats (DB_ID(),null, NULL, NULL, NULL) AS a  
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where avg_fragmentation_in_percent > 30
order by avg_fragmentation_in_percent desc

https://msdn.microsoft.com/en-us/library/ms189858.aspx

Thứ Ba, 14 tháng 3, 2017

SQL IsNullOrEmpty Function

CREATE FUNCTION dbo.IsNullOrEmpty(@text nvarchar(max)) returns bit as
BEGIN
 RETURN IIF( @text IS NULL OR LEN(@text) = 0, 1, 0)
END