SQL Tricks

From Hobowiki

(Redirected from Stupid SQL Tricks)
Jump to: navigation, search

Contents

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') = 1

Obfuscating 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
END

Changing 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
GO

Use 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')
GO

Keep in mind that you will need to restart SQL Server after changing the tempdb location.

Personal tools