Find records where your column has numbers or not SQL Server

Well, after looking for a while online I was kind of surprised nobody have had blogged about this. Maybe I was looking for the wrong keywords I don’t know

I was able to come up with this simple way.

Find records where your column has numbers :

WHERE PATINDEX ( '%[^0-9]%' , [Field Name]) > 1

 

Find only records with number on the column
WHERE PATINDEX ( '%[^0-9]%' , [Field Name]) = 0

Find records where your column does not have numbers

WHERE PATINDEX ( '%[^0-9]%' , [Field Name]) = 1

 

I hope you enjoy this.

Hierarchy Query

This I Took from another Blog but is so cool I have to re post it,

This is to make a level hierarchy query

Only SQL server 2008 and up

with MyCTE AS
(
SELECT      
TYPEID,
ParentTypeId,
TYPE,
1 as level,
CAST(CAST(TYPEID AS BINARY(4)) AS VARBINARY(8000)) AS SortPath
FROM          
TASKTYPE
WHERE      
(ParentTypeId  IS NULL)
UNION ALL

SELECT      
TY.TYPEID,
TY.ParentTypeId ,
TY.TYPE,
EL.level + 1,
CAST(el.SortPath + CAST(TY.TYPEID AS BINARY(4)) AS VARBINARY(8000)) AS SortPath
FROM            TASKTYPE as TY
INNER JOIN MyCTE AS el on TY.ParentTypeId  = el.TypeID
WHERE        (TY.ParentTypeId  IS NOT NULL)

)
Select Typeid, parenttypeid, type = SPACE((level-1)*4)+type , level
from MyCTE
ORDER BY SortPath

Enjoy

Get table size T SQL

Simple and effective your admin will love this


SELECT   DB_NAME() AS DatabaseName
   , object_name(i.object_id) AS TableName
   , ISNULL(i.name, 'HEAP') AS IndexName
   , i.index_id AS IndexID
   , i.type_desc AS IndexType
   , p.partition_number AS PartitionNo
   , p.[rows] AS NumRows
   , au.type_desc AS InType
   , au.total_pages AS NumPages
   , au.total_pages * 8 AS TotKBs
   , au.used_pages * 8 AS UsedKBs
   , au.data_pages * 8 AS DataKBs
FROM sys.indexes i INNER JOIN sys.partitions p
ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units au ON
CASE
WHEN au.[type] in (1,3) THEN p.hobt_id
WHEN au.type = 2 THEN p.partition_id
end = au.container_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.is_ms_shipped <> 1
ORDER BY TableName, i.index_id

Database in lock state?

With this script you will get your panic attack under control

Use <Database>

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE

Easy way to Shrink databases Log

Easy to set up as an scheduled task

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

Same apply to the database file

USE [RC_BI_DW]  DBCC SHRINKFILE (N<Database file name> , 0, TRUNCATEONLY)