DotMim

Select * From sys.tables

Select * from sys.foreign_keys 

select * from sys.indexes

Select * from sys.check_constraints

 

Récupérer les colonnes d'une base de données :

Select * from sys.all_columns 

 

Faisons un peu de liaisons entre tout ça :

 

En passant en paramètre le nom de la table :

Declare @TableName NVarchar(255)
Set @TableName = 'order_details'

SELECT *
FROM sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id

 

Si on veut avoir quelque chose de plus "lisible" maintenant, on peut faire quelque chose dans le style :

 

Récupérer les clés primaires d'une table :

Declare @TableName NVarchar(255)

Set @TableName = 'order_details'

-- Clé primaires (Index CLUSTERED)
SELECT
i.name AS [Primary Key Name],
serverproperty(N'Servername') as ServerName,
db_name() as DataBaseName ,
tbl.name as TableName,
SCHEMA_NAME(tbl.schema_id) as SchemaName
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
WHERE i.is_primary_key=1 and tbl.name= @TableName

-- Index non clé primaire
SELECT
i.name AS [Index Name],
serverproperty(N'Servername') as ServerName,
db_name() as DataBaseName ,
tbl.name as TableName,
SCHEMA_NAME(tbl.schema_id) as SchemaName
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
WHERE i.is_primary_key=0 and tbl.name= @TableName

-- Foreign keys
SELECT
cstr.name AS [Foreign key Name],
serverproperty(N'Servername') as ServerName,
db_name()as DataBaseName,
tbl.name as TableName,
SCHEMA_NAME(tbl.schema_id) as SchemaName ,
cstr.create_date AS [CreateDate]
FROM
sys.tables AS tbl
INNER JOIN sys.foreign_keys AS cstr ON cstr.parent_object_id=tbl.object_id
WHERE tbl.name= @TableName