SQL Tricks
Most of these are done using MS SQL Server
How to create a check constraint to limit a field to a set of values
NOT DONE
SQL Check constraints are objects directly associated with a table, unlike SQL Rules in MS SQL Server (depreciated). To create a constraint you just need to either create or alter an existing table. In this example I am going to alter an existing table to add constraints.
CREATE TABLE [DOC].[TimeDelta]
(
[TimeDeltaID] [int] IDENTITY(1, 1)
NOT NULL,
[TimeStart] [datetime] NOT NULL,
[TimeEnd] [datetime] NOT NULL,
[IsListReceived] [char](1) NOT NULL
CONSTRAINT [DF_TimeDelta_IsListReceived] DEFAULT ( 'N' ),
CONSTRAINT [PK_TimeDelta] PRIMARY KEY CLUSTERED ( [TimeDeltaID] ASC )
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
)
ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [DOC].[TimeDelta]
WITH CHECK
ADD CONSTRAINT [CK_TimeDelta_IsListReceived] CHECK ( ( [IsListReceived] = 'N'
OR [IsListReceived] = 'Y'
) )
GO
ALTER TABLE [DOC].[TimeDelta]
CHECK CONSTRAINT [CK_TimeDelta_IsListReceived]How to return a list of all the parameters of a stored procedure
Since I typically use this in a program, I pass the name of the stored procedure into this string with formatting methods. To use this outside of a program, simply replace the {0} with the full name of the stored procedure (including schema).
This SQL will return a list of all the parameters of a stored procedure, including their type and length.
SUBSTRING(SP.name,2,LEN(SP.name)) as 'ParameterName'
, ST.name as 'Type',SP.max_length as 'FieldLength'
FROM
sys.parameters as SP
JOIN
sys.types as ST
ON SP.system_type_id = ST.system_type_id
JOIN
sys.procedures AS SPC
ON object_name(SP.object_id) = SPC.name
JOIN
sys.schemas AS SCH
ON SPC.schema_id = SCH.schema_id
WHERE
SCH.name + '.' +SPC.name = '{0}'Interrogating tables and columns for their metadata in SQL Server 2005
First Method
SELECT kcu.TABLE_SCHEMA,
kcu.TABLE_NAME,
kcu.CONSTRAINT_NAME,
tc.CONSTRAINT_TYPE,
kcu.COLUMN_NAME,
kcu.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
AND kcu.TABLE_NAME = tc.TABLE_NAME
WHERE tc.CONSTRAINT_TYPE IN ( 'PRIMARY KEY', 'UNIQUE' )
ORDER BY kcu.TABLE_SCHEMA,
kcu.TABLE_NAME,
tc.CONSTRAINT_TYPE,
kcu.CONSTRAINT_NAME,
kcu.ORDINAL_POSITION ;Second Method
SELECT C.*
,COL_LENGTH(C.TABLE_SCHEMA + '.' + C.TABLE_NAME, C.COLUMN_NAME) AS COLUMN_LENGTH
,COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME),
C.COLUMN_NAME, 'IsComputed') AS IS_COMPUTED
,COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME),
C.COLUMN_NAME, 'IsIdentity') AS IS_IDENTITY
,COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME),
C.COLUMN_NAME, 'IsRowGuidCol') AS IS_ROWGUIDCOL
FROM INFORMATION_SCHEMA.COLUMNS AS C
WHERE C.TABLE_NAME='Action'Third Method
SELECT SCHEMA_NAME(OBJECTPROPERTY(OBJECT_ID, 'SCHEMAID')) AS TABLE_SCHEMA,
OBJECT_NAME(OBJECT_ID) AS TABLE_NAME,
NAME AS COLUMN_NAME
FROM SYS.COLUMNS
WHERE COLUMNPROPERTY(OBJECT_ID, NAME, 'IsIdentity') = 1Obfuscating SQL by rearranging row data
This method of obfuscation simply relies on the connectedness of data. This method should work for most uses, while it is recommended that especially sensitive data should be obfuscated further, this is a good starting place. All you need to do to use this code is the name of the table, the column, and the data type of the column, and it will rearrange all the data in that column so the original data links cannot be determined.
Keep in mind, this procedure is vulnerable to SQL injection, and should not be exposed in a production system, it should only be used in development environments.
CREATE PROCEDURE Obfuscate @tablename varchar(50), @columnname varchar(50), @columntype varchar(50) AS
BEGIN
PRINT '------------'
PRINT 'Randomizing Table:' + @tablename + ' Column:' + @columnname + ' Type:'+@columntype
DECLARE @optionalCollate VARCHAR(50)
SET @optionalCollate=''
SET @columntype = LOWER(@columntype)
IF (CHARINDEX('char', @columntype COLLATE SQL_Latin1_General_CP1_CI_AS) <> 0)
OR (CHARINDEX('nchar', @columntype COLLATE SQL_Latin1_General_CP1_CI_AS) <> 0)
OR (CHARINDEX('varchar', @columntype COLLATE SQL_Latin1_General_CP1_CI_AS) <> 0)
OR (CHARINDEX('nvarchar', @columntype COLLATE SQL_Latin1_General_CP1_CI_AS) <> 0)
OR (CHARINDEX('text', @columntype COLLATE SQL_Latin1_General_CP1_CI_AS) <> 0)
OR (CHARINDEX('ntext', @columntype COLLATE SQL_Latin1_General_CP1_CI_AS) <> 0)
BEGIN
SET @optionalCollate = ' COLLATE '+cast( DATABASEPROPERTYEX ( db_name(), N'Collation' ) as varchar(128) )
END
DECLARE @sql NVARCHAR(MAX)
SET NOCOUNT ON
SELECT @sql =
'CREATE TABLE #tempHold (ID int,Old'+@columnname+' '+@columntype+@optionalCollate+',New'+@columnname+' '+@columntype+@optionalCollate+')
CREATE TABLE #seed (ID int,New'+@columnname+' '+@columntype+@optionalCollate+')
INSERT #tempHold (ID, Old'+@columnname+') SELECT DISTINCT ROW_NUMBER() OVER(ORDER BY '+@columnname+') as ID, C.'+@columnname+' as ''Old'+@columnname+''' FROM (SELECT DISTINCT ['+@columnname+'] FROM '+@tablename+' WHERE '+@columnname+' IS NOT NULL) C
DECLARE @NeedMoreRandomness INT
DECLARE @ActualRecords INT
SELECT @ActualRecords = COUNT(*) FROM #tempHold
PRINT ''Creating '' + CAST(@ActualRecords as VARCHAR(20)) + '' unique rows of data.''
SELECT DISTINCT @ActualRecords = COUNT('+@columnname+') FROM '+@tablename+' WHERE '+@columnname+' IS NOT NULL
IF @ActualRecords <= 1 RAISERROR(''Not enough records to perform obfuscation successfully.'' , 16, 1)
START:
SET @NeedMoreRandomness = @ActualRecords
BEGIN TRY
WHILE @NeedMoreRandomness > 0
BEGIN
INSERT #seed (ID,New'+@columnname+') SELECT ROW_NUMBER() OVER(ORDER BY NEWID()) as ID, Old'+@columnname+' FROM #tempHold
UPDATE R SET New'+@columnname+' = s.New'+@columnname+' FROM #tempHold R JOIN #seed s ON s.ID = R.ID WHERE R.Old'+@columnname+' <> S.New'+@columnname+' AND R.New'+@columnname+' IS NULL
UPDATE #tempHold SET New'+@columnname+' = NULL WHERE Old'+@columnname+'=New'+@columnname+'
SELECT @NeedMoreRandomness = COUNT(*) FROM #tempHold WHERE New'+@columnname+' IS NULL
IF @NeedMoreRandomness > 0
BEGIN
PRINT CAST(@NeedMoreRandomness AS VARCHAR(20)) + '' duplicates found, re-seeding.''
DELETE FROM #seed
END
END
UPDATE C SET '+@columnname+' = R.New'+@columnname+' FROM '+@tablename+' C JOIN #tempHold R ON R.Old'+@columnname+' = C.'+@columnname+'
PRINT ''Successfully updated ''+CAST(@@ROWCOUNT AS VARCHAR(50))+'' rows.''
END TRY
BEGIN CATCH
PRINT ''Error encountered: ''+ERROR_MESSAGE()+'', retrying.''
GOTO START
END CATCH'
EXEC sp_executesql @sql
SET NOCOUNT OFF
ENDChanging temp database location in filesystem
When you are working with databases in SQL Server, sometimes you might find yourself working with a great deal of temp data that can cause the tempdb to balloon well above it's established maximum. If you do find yourself in that situation, you might want to change the location of the tempdb to a better spot with more space or just to alieveate the HD where the tempdb is located. In those situations you can run these little commands to change the location:
Use this first to find out where your tempdb is currently stored:
USE TempDB
GO
EXEC sp_helpfile
GOUse this to change the location of the temp db
USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:datatemplog.ldf')
GOKeep in mind that you will need to restart SQL Server after changing the tempdb location. <analytics uacct="UA-868295-1"></analytics>