Saturday, February 11, 2012

Prevent a certain text string from being entered in a SQL Server database

A weired requirement about how to prevent a string value at the database level...at EE I advised to use a cursor to iterate over the tables, grab all the string data types and add a check constraint to that column.



Test:

  CREATE TABLE help
 (id INT IDENTITY(1,1),
 Fname VARCHAR(100) )
 
 
 ALTER TABLE help
 ADD CONSTRAINT ckname CHECK (Fname not LIKE '%sauerkraut%' )
  
  INSERT  help SELECT  ('sau')
  INSERT  help SELECT  ('I love sauerkraut very much')
  INSERT  help SELECT  ('sauerkrautttttt')
                                            

Cursor Script:


DECLARE @Stmt         NVARCHAR(4000),
        @DBName       SYSNAME

SET @DBName = DB_NAME()

DECLARE @CName    VARCHAR(255),
        @TName    SYSNAME,
        @OName    SYSNAME,
        @Sql      VARCHAR(8000)
       
DECLARE curcolumns CURSOR READ_ONLY FORWARD_ONLY LOCAL FOR

SELECT C.Name AS Colname
  , T.Name AS DType
  , QUOTENAME(U.Name) + '.' + QUOTENAME(O.Name) AS Tbl


FROM
 syscolumns C
 INNER JOIN systypes T
  ON C.xtype = T.xtype
 INNER JOIN sysobjects O
  ON C.ID = O.ID
 INNER JOIN sysusers u
  ON O.uid = u.uid

WHERE
 T.Name IN ('varchar', 'char', 'text', 'nchar', 'nvarchar', 'ntext')
 AND O.xtype IN ('U')
 AND objectProperty(O.ID, 'ismsshipped') = 0

ORDER BY
 3
  , 1


OPEN curcolumns

SET XACT_ABORT ON

BEGIN TRAN

FETCH curcolumns INTO @CName, @TName, @OName

WHILE @@FETCH_STATUS = 0

BEGIN

 SET @Sql = 'ALTER TABLE ' + @OName + 
 ' ADD CONSTRAINT chk_' + @CName +' Check ( '+  @CName +' Not like ''%sauerkraut%'' )'


 --EXEC (@Sql) -- change this to print if you need only the script, not the action
PRINT @Sql

 FETCH curcolumns INTO @CName, @TName, @OName
END

CLOSE curcolumns

DEALLOCATE curcolumns

COMMIT TRAN