SQL Tricks

From Hobowiki
(Redirected from Stupid SQL Tricks)
Jump to navigation Jump to search

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. <source lang="sql"> 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]

</source>

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. <source lang="sql"> 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}'

</source>

Interrogating tables and columns for their metadata in SQL Server 2005

First Method <source lang="sql"> 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 ;

</source>

Second Method <source lang="sql"> 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' </source>

Third Method <source lang="sql"> 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 </source>

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.

<source lang="sql"> CREATE PROCEDURE Obfuscate @tablename varchar(50), @columnname varchar(50), @columntype varchar(50) AS BEGIN PRINT '------------' PRINT 'Randomizing Table:' + @tablename + ' Column:' + @columnname + ' Type:'[email protected] 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'[email protected]+' '[email protected][email protected]+',New'[email protected]+' '[email protected][email protected]+') CREATE TABLE #seed (ID int,New'[email protected]+' '[email protected][email protected]+') INSERT #tempHold (ID, Old'[email protected]+') SELECT DISTINCT ROW_NUMBER() OVER(ORDER BY '[email protected]+') as ID, C.'[email protected]+' as Old'[email protected]+' FROM (SELECT DISTINCT ['[email protected]+'] FROM '[email protected]+' WHERE '[email protected]+' 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('[email protected]+') FROM '[email protected]+' WHERE '[email protected]+' 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'[email protected]+') SELECT ROW_NUMBER() OVER(ORDER BY NEWID()) as ID, Old'[email protected]+' FROM #tempHold UPDATE R SET New'[email protected]+' = s.New'[email protected]+' FROM #tempHold R JOIN #seed s ON s.ID = R.ID WHERE R.Old'[email protected]+' <> S.New'[email protected]+' AND R.New'[email protected]+' IS NULL UPDATE #tempHold SET New'[email protected]+' = NULL WHERE Old'[email protected]+'=New'[email protected]+' SELECT @NeedMoreRandomness = COUNT(*) FROM #tempHold WHERE New'[email protected]+' IS NULL IF @NeedMoreRandomness > 0 BEGIN PRINT CAST(@NeedMoreRandomness AS VARCHAR(20)) + duplicates found, re-seeding. DELETE FROM #seed END END

UPDATE C SET '[email protected]+' = R.New'[email protected]+' FROM '[email protected]+' C JOIN #tempHold R ON R.Old'[email protected]+' = C.'[email protected]+' 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 </source>

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: <source lang="sql"> USE TempDB GO EXEC sp_helpfile GO </source>

Use this to change the location of the temp db <source lang="sql"> 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 </source>

Keep in mind that you will need to restart SQL Server after changing the tempdb location. <analytics uacct="UA-868295-1"></analytics>