SQL Tricks

From Hobowiki

Jump to: navigation, search

Contents

Most of these are done using MS SQL Server

[edit] 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 and 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]

[edit] 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}'

[edit] Interrogating a 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

Retrieved from "http://www.defhoboz.biz/wiki/SQL_Tricks"
Personal tools