SQL

In SQL you can use PATINDEX (which makes use of Regular Expressions) to extract all the numbers from within a string as shown: DECLARE @strNumbers VARCHAR(100) = ‘I was born

There will be times when you need to pass a list of values as a singular parameter to an SQL Stored Procedure. Problem is: SQL doesn’t support this. You will

The SQL FORMAT() function is used to format how a field should be displayed. You can format dates in a column in the following manner: SELECT FullNames, Surname, UserID, FORMAT(Now(),’YYYY-MM-DD’)

Sometimes you will have a situation where you would want a certain query, or Stored Procedure to be repeated on a daily, weekly or monthly basis (or any time increment

You can use IF NOT EXISTS?to check whether a Primary Key is missing and add it as follows: IF NOT EXISTS (SELECT * FROM sys.tables tINNER JOIN sys.schemas s ON

You can identify tables with a huge column count in the following way: DECLARE @limit INT;SET @limit = 30; –MAX 30 Columns;WITH c([object_id], [column count]) AS( SELECT [object_id], COUNT(*) FROM

You can use the following snippet to quickly find out a column’s name. COL_NAME accepts two parameters: TableID and the ordinal position. SELECT COL_NAME(object_id(‘waybill’),2) AS col_name

Learn how to use FILEPROPERTY to check for free space in a database. SELECT DB_NAME() AS DBName, name AS FileName, size/128.0 AS MB, size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS INT)/128.0 AS

The?following?code?enables?you?to?identify?all?of?your?foreign?keys?in?a?particular?database. DUMMY?TEXT:?This?is?some?dummy?text.?Hopefully?there?is?no?issues?in?submitting?this?tip?on?devx.com SELECT?PKTABLE_QUALIFIER?=?CONVERT(SYSNAME,DB_NAME()),? ???????PKTABLE_OWNER?=?CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),? ???????PKTABLE_NAME?=?CONVERT(SYSNAME,O1.NAME),? ???????PKCOLUMN_NAME?=?CONVERT(SYSNAME,C1.NAME),? ???????FKTABLE_QUALIFIER?=?CONVERT(SYSNAME,DB_NAME()),? ???????FKTABLE_OWNER?=?CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),? ???????FKTABLE_NAME?=?CONVERT(SYSNAME,O2.NAME),? ???????FKCOLUMN_NAME?=?CONVERT(SYSNAME,C2.NAME),? ???????–?Force?the?column?to?be?non-nullable?(see?SQL?BU?325751)? ???????–KEY_SEQ?????????????=?isnull(convert(smallint,k.constraint_column_id),?sysconv(smallint,0)),? ???????UPDATE_RULE?=?CONVERT(SMALLINT,CASE?OBJECTPROPERTY(F.OBJECT_ID,’CnstIsUpdateCascade’)?? ????????????????????????????????????????WHEN?1?THEN?0? ????????????????????????????????????????ELSE?1? ??????????????????????????????????????END),? ???????DELETE_RULE?=?CONVERT(SMALLINT,CASE?OBJECTPROPERTY(F.OBJECT_ID,’CnstIsDeleteCascade’)?? ????????????????????????????????????????WHEN?1?THEN?0? ????????????????????????????????????????ELSE?1? ??????????????????????????????????????END),? ???????FK_NAME?=?CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),? ???????PK_NAME?=?CONVERT(SYSNAME,I.NAME),? ???????DEFERRABILITY?=?CONVERT(SMALLINT,7)???? FROM???SYS.ALL_OBJECTS?O1,? ???????SYS.ALL_OBJECTS?O2,? ???????SYS.ALL_COLUMNS?C1,? ???????SYS.ALL_COLUMNS?C2,? ???????SYS.FOREIGN_KEYS?F? ???????INNER?JOIN?SYS.FOREIGN_KEY_COLUMNS?K? ?????????ON?(K.CONSTRAINT_OBJECT_ID?=?F.OBJECT_ID)?