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,
WLW = Windows Live Writer
Just testing the WLW Polaroid plugin, it’s easy! Features: sizing, tilt image, add caption text and add corners.
Btw, for easy code pasting with syntax highlighting, I use the WLW Code Plugin. Also recommended!
Best regards,
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,
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:
- Go into the registry - HKLM\SYSTEM\CurrentControlSet\Control\Terminal Server,
- Create/edit the DWORD value KeepAliveEnable.
- Set it to 1.
- 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.
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