Find stored procedures, tables and functions in T-SQL

When developing a lot in database, it can be useful to search for database objects that contain (partly) a specific name. The stored procedure script below allows you to perform such a search.

/*
Find stored procedures / tables / functions
*/
CREATE procedure dbo.pr_FindIt   
(   
 @find varchar(128)   
)   
   
as   
    
SELECT    
 TABLE_SCHEMA AS OBJECT_SCHEMA,    
 TABLE_NAME AS OBJECTNAME,    
 TABLE_TYPE AS OBJECT_TYPE,   
 null as CREATED,   
 null as LAST_ALTERED,   
 null as SQL_DATA_ACCESS   
FROM INFORMATION_SCHEMA.TABLES    
WHERE TABLE_NAME LIKE '%' + @find + '%'    
   
union   
   
SELECT    
 ROUTINE_SCHEMA AS OBJECT_SCHEMA,    
 ROUTINE_NAME AS OBJECTNAME,    
 ROUTINE_TYPE AS OBJECT_TYPE,   
 CREATED,    
 LAST_ALTERED,    
 SQL_DATA_ACCESS    
FROM INFORMATION_SCHEMA.ROUTINES    
WHERE ROUTINE_NAME LIKE '%' + @find + '%'    
   
ORDER BY OBJECT_SCHEMA, OBJECT_TYPE, OBJECTNAME   
   
go

 

Other possibility is to use a simple query (example is for a stored procedure):

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%text_to_search_for%'
AND ROUTINE_TYPE='PROCEDURE'

 

Hope this helps,

Defragmentation script for SQL Server

When you SQL server database becomes slower over time without apparent reasons, probably you’re facing a fragmentation problem. Database table fragmentation is one of the major performance killers.

With the following maintenance script you defrag you tables and indexes.

-- =============================================
-- Copyright by Author Sajal Dam, ISBN 1590594215 
-- Description: Performs defragmentation actions
-- tables and indexes in your database.
-- Execute this script once a month or more often 
-- if necessary
-- =============================================
CREATE PROC [dbo].[pr_Sys_DefragDatabase]
AS
SET NOCOUNT ON
--Create temporary table to hold DBCC SHOWCONTIG output
CREATE TABLE #FragmentationResult(
    ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
    IndexId INT, [Level] INT, Pages INT, [Rows] INT,
    MinimumRecordSize INT, MaximumRecordSize INT,
    AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
    ExtentSwitches INT, AverageFreeBytes FLOAT,
    AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
    ActualCount INT, LogicalFragmentation FLOAT,
    ExtentFragmentation FLOAT
)
--Create temporary table to hold tables/indexes that require
-- defragmentation
CREATE TABLE #Defragmentation(
    [id] INT IDENTITY,
    ObjectName VARCHAR(255),
    IndexName VARCHAR(255),
    ScanDensity FLOAT
)

--Identify all user tables in the current database to analyze
-- fragmentation
SELECT SO.id as [id], SO.name as [name] INTO #UserTables
    FROM sysobjects SO
    INNER JOIN sysusers SU
    ON SO.uid = SU.uid
    WHERE type = 'U'
    AND SU.name = 'dbo' 
    ORDER BY [id]
 
--Determine fragmentation of every user table/index
DECLARE @id INT, @name VARCHAR(255), @TableCnt INT
SET @id = 0
SELECT @TableCnt = COUNT(*) FROM #UserTables
    WHILE @TableCnt > 0
    BEGIN
        SELECT TOP 1 @id=[id], @name=[name]
        FROM #UserTables
        WHERE [id] > @id
        
        INSERT INTO #FragmentationResult
        EXEC('DBCC SHOWCONTIG([' + @name + '])
            WITH ALL_INDEXES, TABLERESULTS')
        SET @TableCnt = @TableCnt - 1
    END
    
--Determine user tables/indexes that require defragmentation
INSERT INTO #Defragmentation
SELECT ObjectName, IndexName, ScanDensity
    FROM #FragmentationResult
    WHERE ScanDensity < 50 --Scan Density is low
    AND LogicalFragmentation > 9 --Logical Scan Fragmentation is high
    AND PAGES > 8 --Not a very small table
DROP TABLE #FragmentationResult

--Defragment tables/indexes with high fragmentation
DECLARE @oname VARCHAR(255), @iname VARCHAR(255), @sdensity FLOAT
SET @id = 0
SELECT @TableCnt = COUNT(*) FROM #Defragmentation
WHILE @TableCnt > 0
BEGIN
    SELECT TOP 1 @id=[id], @oname = ObjectName, 
        @iname = IndexName, @sdensity = ScanDensity
        FROM #Defragmentation
        WHERE [id] > @id
    PRINT '** De-fragmentation #' + CAST(@id AS VARCHAR(15))+ ' **'
    PRINT 'DBCC DBREINDEX on [' + @oname + '].[' + @iname
            + '] with ScanDensity = ' + CAST(@sdensity AS VARCHAR(15)) + '%'
    DBCC DBREINDEX(@oname, @iname)
    SET @TableCnt = @TableCnt - 1
END

--Release resources
DROP TABLE #UserTables
DROP TABLE #Defragmentation
SET NOCOUNT OFF

 

Hope this help,

Keep Remote Desktop Session Alive

Terminal Services Remote Desktop automatically kills session which are not actively used for some times. This sometimes annoys me, since some connections remain open and used all day (like development server environments. It's possible to enable a keep alive setting on the server by editing the Windows registry of the server.

The simple steps:

 

  1. Go into the registry - HKLM\SYSTEM\CurrentControlSet\Control\Terminal Server,
  2. Create/edit the DWORD value KeepAliveEnable.
  3. Set it to 1.
  4. This should turn on Keep Alive and send a heartbeat with the system. Done!
Disclaimer note: please make sure what you are doing & make backups.

 

 

Mobile and Tablet Browser market share

Interesting site with a lot of browser market share statistics. Safari is big on mobile & tablet (61%), likely caused by their Iphone and Ipad succes. Android is growing, but still not even the half of their main competitor (currently 26% marketshare). Microsoft has a lot to gain with their Windows 8 phone and tablet. See for more: http://www.netmarketshare.com/browser-market-share.aspx?qprid=0&qpcustomd=1&clearaf=1&qptimeframe=M