How to get the constraints on a table

By constraints I mean checks, not nulls, primary key constraint, foreign key constraint

What SQL query to use

Visual Studio 2008 / SQL S开发者_JS百科erver 2005 Express

Like for example to get table names

commandString = "SELECT * FROM INFORMATION_SCHEMA.TABLES";

So what to get constraints.

not nulls is not a constraint, you can check for that in information_schema.columns

for constraints

select *
 from information_schema.table_constraints

for columns that don't allow nulls

select *
 from information_schema.columns
 where is_nullable = 'NO'

You can use the sys.objects table to get this info, check this query to get all Constraints of an table .

SELECT OBJECT_NAME(OBJECT_ID) AS [Constraint],SCHEMA_NAME(schema_id) AS [Schema],
OBJECT_NAME(parent_object_id) AS [Table],
type_desc AS [Type] FROM sys.objects 
WHERE 1=1  
AND OBJECT_NAME(parent_object_id)='YOURTABLENAME'
AND type_desc LIKE '%CONSTRAINT' 

Give this a try, it is a little query I've put together, and should get you going in the right direction. You can remove the comments as necessary to filter for the specific schema/table/column that you want.

/*Run a query like the listed below, from it you can see:  

+ schema name
+ table/view name
+ table type (like: SYSTEM_TABLE, VIEW, SQL_TABLE_VALUED_FUNCTION, USER_TABLE, SQL_INLINE_TABLE_VALUED_FUNCTION, INTERNAL_TABLE)
+ column name
+ column data type (including length, precision, etc)
+ Nullability
+ identity (seed, increment, and current value)
+ check constraint definition
+ computed column definition
+ complete index and primary key info, every index/PK that this column is contained in, includes index type (unique/clustered/PK etc) as well as all index/pk columns concatenated together, including covering columns 

Needs SQL Server 2005+ to run:
*/
--DECLARE @SchemaNameSearch   sysname
--       ,@TableNameSearch    sysname
--       ,@ColumnNameSearch   sysname
--SELECT @SchemaNameSearch  ='YourSchemaName'
--      ,@TableNameSearch   ='YourTableName'
--      ,@ColumnNameSearch  ='YourColumnName'

;WITH AllIndexes AS
(
SELECT
    s.name+'.'+t.name AS TableName
        ,xx.name AS IndexName
        ,'['
            +CASE WHEN xx.is_unique=1 THEN 'UNIQUE ' ELSE '' END
            +xx.type_desc
            +CASE WHEN xx.is_primary_key=1 THEN ' PRIMARY KEY' ELSE '' END
            +ISNULL(
                       ' ('
                       +STUFF(
                              (SELECT 
                                    ', '+c.Name
                                   FROM sys.objects                           o
                                       LEFT OUTER JOIN sys.indexes            x ON o.object_id=x.object_id
                                       LEFT OUTER JOIN sys.index_columns     xc ON o.object_id=xc.object_id AND x.index_id=xc.index_id 
                                       LEFT OUTER JOIN sys.columns            c ON o.object_id=c.object_id AND c.column_id=xc.column_id
                                  WHERE oo.object_id=o.object_id AND xc.column_id IS NOT NULL
                                      AND xx.index_id=x.index_id AND xc.is_included_column=0
                                      --
                                      --REMOVE comments to filter the query
                                      --AND o.Name=@TableNameSearch 
                                      --
                                  ORDER BY o.object_ID,xc.key_ordinal
                                  FOR XML PATH('') 
                              )
                              ,1,2, ''
                            )
                       +')'
                       ,''
                   )
            +ISNULL(
                       ' INCLUDE ('
                       +STUFF(
                               (SELECT 
                                     ', '+c.Name
                                    FROM sys.objects                           o
                                        LEFT OUTER JOIN sys.indexes            x ON o.object_id=x.object_id
                                        LEFT OUTER JOIN sys.index_columns     xc ON o.object_id=xc.object_id AND x.index_id=xc.index_id 
                                        LEFT OUTER JOIN sys.columns            c ON o.object_id=c.object_id AND c.column_id=xc.column_id
                                   WHERE oo.object_id=o.object_id AND xc.column_id IS NOT NULL
                                       AND xx.index_id=x.index_id AND xc.is_included_column=1
                                       --
                                       --REMOVE comments to filter the query
                                       --AND o.Name=@TableNameSearch 
                                       --
                                   ORDER BY o.object_ID,xc.key_ordinal
                                   FOR XML PATH('') 
                               )
                               ,1,2, ''
                             )
                       +')'
                       ,''
                   )
            +']'  AS IndexInfo
          ,cc.object_id,cc.column_id,xx.is_primary_key
      FROM sys.objects                        oo
          INNER JOIN sys.tables                t ON oo.object_id=t.object_id
          INNER JOIN sys.schemas               s ON t.schema_id=s.schema_id
          LEFT OUTER JOIN sys.indexes         xx ON oo.object_id=xx.object_id
          LEFT OUTER JOIN sys.index_columns  xxc ON oo.object_id=xxc.object_id AND xx.index_id=xxc.index_id 
          LEFT OUTER JOIN sys.columns         cc ON oo.object_id=cc.object_id AND cc.column_id=xxc.column_id
      --
      --REMOVE comments to filter the query
      --WHERE s.name =@SchemaNameSearch
      --    AND oo.Name=@TableNameSearch 
      --    AND s.name=@ColumnNameSearch
      --
)
SELECT
    sh.name+'.'+o.name AS ObjectName
        ,o.type_desc AS ObjectType
        ,s.name as ColumnName
        ,CASE
             WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
             WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
             WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
             ELSE t.name
         END AS DataType

        ,CASE
             WHEN s.is_nullable=1 THEN 'NULL'
            ELSE 'NOT NULL'
        END AS Nullable
        ,CASE
             WHEN All_IXs.object_id IS NOT NULL THEN All_IXs.IndexInfo
             ELSE NULL
         END AS IndexInfo
        ,CASE
             WHEN ic.column_id IS NULL THEN ''
             ELSE ' identity('+ISNULL(CONVERT(varchar(10),ic.seed_value),'')+','+ISNULL(CONVERT(varchar(10),ic.increment_value),'')+')='+ISNULL(CONVERT(varchar(10),ic.last_value),'null')
         END
        +CASE
             WHEN sc.column_id IS NULL THEN ''
             ELSE ' computed('+ISNULL(sc.definition,'')+')'
         END
        +CASE
             WHEN cc.object_id IS NULL THEN ''
             ELSE ' check('+ISNULL(cc.definition,'')+')'
         END
            AS MiscInfo
    FROM sys.objects                           o
        INNER JOIN sys.schemas                sh on o.schema_id=sh.schema_id
        INNER JOIN sys.columns                 s ON o.object_id=s.object_id
        INNER JOIN sys.types                   t ON s.system_type_id=t.system_type_id and t.is_user_defined=0
        LEFT OUTER JOIN sys.identity_columns  ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id
        LEFT OUTER JOIN sys.computed_columns  sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id
        LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id
        LEFT OUTER JOIN sys.indexes            x ON o.object_id=x.object_id AND x.is_primary_key=1
        LEFT OUTER JOIN sys.index_columns     xc ON o.object_id=xc.object_id AND x.index_id=xc.index_id AND s.column_id=xc.column_id
        LEFT OUTER JOIN (SELECT --build the concatenated PK here
                             oo.object_id,ss.column_id
                                  ,STUFF(
                                             (
                                              SELECT 
                                                   ', '+IndexInfo
                                                  FROM AllIndexes                           i
                                                  WHERE oo.object_id=i.object_id AND ss.column_id=i.column_id
                                                  ORDER BY i.object_ID ASC,i.is_primary_key DESC,i.column_id ASC
                                                  FOR XML PATH('') 
                                             )
                                            ,1,2, ''
                                        ) AS IndexInfo
                                FROM sys.objects            oo
                                    INNER JOIN sys.columns  ss ON oo.object_id=ss.object_id
                                --
                                --REMOVE comments to filter the query
                                --WHERE oo.Name=@TableNameSearch 
                                --
                        ) All_IXs ON o.object_id=All_IXs.object_id AND s.column_id=All_IXs.column_id
    --
    --REMOVE comments to filter the query
    --WHERE sh.name =@SchemaNameSearch
    --    AND o.Name=@TableNameSearch 
    --    AND s.name=@ColumnNameSearch
    --
    ORDER BY sh.name+'.'+o.name,s.column_id

As well as the other methods, you can use sp_help (ALT-F1 by default)

    CREATE TABLE testTable2
(
    test int
    )

ALTER TABLE testTable2
ADD CHECK (test>40)

sp_help testTable2

will yield the constraint and the conditions. This is also shows FKs, UKs, etc.

this gives you Name of the constraint , type and table [MS-SQL 2000]

Select s.name,
(Case 
when s.xtype='PK' then 'PRIMARY KEY' 
when s.xtype='F'  then 'FOREIGN KEY'
when s.xtype='D' then 'DEFAULT'      -- add case for others if you wish
else s.xtype
end) as [Constraint Type],
Tab.[Name] as [Table Name]--,Col.[Name] As [Column Name]
from sysobjects as s
Inner Join (Select [Name],[ID] From SysObjects Where XType = 'U') As Tab
On Tab.[ID] = s.[Parent_Obj]
Inner Join sysconstraints 
On sysconstraints.Constid = s.[ID]