Comment lister la clé primaire d'une table SQL Server?
24 réponses
SELECT Col.Column_Name from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE
Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY'
AND Col.Table_Name = '<your table name>'
il est généralement recommandé maintenant d'utiliser les vues sys.* sur INFORMATION_SCHEMA dans SQL Server, donc à moins que vous ne planifiez la migration des bases de données, je les utiliserais. Voici comment vous le feriez avec le sys.* vues:
SELECT
c.name AS column_name,
i.name AS index_name,
c.is_identity
FROM sys.indexes i
inner join sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
inner join sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1
and i.object_ID = OBJECT_ID('<schema>.<tablename>');
il s'agit d'une solution qui n'utilise que sys - tables.
il Liste toutes les clés primaires dans la base de données. Il retourne schéma, nom de la table, nom de la colonne et le bon ordre de tri de la colonne pour chaque clé primaire.
si vous voulez obtenir la clé primaire pour une table spécifique, alors vous devez filtrer sur SchemaName et TableName .
IMHO, cette solution est très générique et n'utilise aucune chaîne de caractères littéraux, de sorte qu'elle fonctionnera sur n'importe quelle machine.
select
s.name as SchemaName,
t.name as TableName,
tc.name as ColumnName,
ic.key_ordinal as KeyOrderNr
from
sys.schemas s
inner join sys.tables t on s.schema_id=t.schema_id
inner join sys.indexes i on t.object_id=i.object_id
inner join sys.index_columns ic on i.object_id=ic.object_id
and i.index_id=ic.index_id
inner join sys.columns tc on ic.object_id=tc.object_id
and ic.column_id=tc.column_id
where i.is_primary_key=1
order by t.name, ic.key_ordinal ;
Voici une autre façon de répondre à la question obtenir la clé primaire de table en utilisant la requête sql :
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA+'.'+CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND TABLE_NAME = '<your table name>'
il utilise KEY_COLUMN_USAGE pour déterminer les contraintes pour une table donnée
Puis utilise OBJECTPROPERTY(id, 'IsPrimaryKey') pour déterminer si chacun est une clé primaire
utilise MS SQL Server vous pouvez faire ce qui suit:
--List all tables primary keys
select * from information_schema.table_constraints
where constraint_type = 'Primary Key'
vous pouvez également filtrer sur la colonne table_name si vous voulez une table spécifique.
j'aime la technique INFORMATION_SCHEMA, mais un autre que j'ai utilisé est: exec sp_pkeys 'table '
--il s'agit d'une autre Version modifiée qui est aussi un exemple de requête en co-relation
SELECT TC.TABLE_NAME as [Table_name], TC.CONSTRAINT_NAME as [Primary_Key]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.TABLE_NAME IN
(SELECT [NAME] AS [TABLE_NAME] FROM SYS.OBJECTS
WHERE TYPE = 'U')
cela devrait énumérer toutes les contraintes (clé primaire et clés étrangères ) et à la fin de la requête mettre le nom de la table
/* CAST IS DONE , SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT*/
WITH ALL_KEYS_IN_TABLE (CONSTRAINT_NAME,CONSTRAINT_TYPE,PARENT_TABLE_NAME,PARENT_COL_NAME,PARENT_COL_NAME_DATA_TYPE,REFERENCE_TABLE_NAME,REFERENCE_COL_NAME)
AS
(
SELECT CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)) ,
CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)) ,
PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)) ,
PARENT_COL_NAME=CAST ( PKnUKEYCol.name AS VARCHAR(30)) ,
PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,
REFERENCE_TABLE_NAME='' ,
REFERENCE_COL_NAME=''
FROM sys.key_constraints as PKnUKEY
INNER JOIN sys.tables as PKnUTable
ON PKnUTable.object_id = PKnUKEY.parent_object_id
INNER JOIN sys.index_columns as PKnUColIdx
ON PKnUColIdx.object_id = PKnUTable.object_id
AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
INNER JOIN sys.columns as PKnUKEYCol
ON PKnUKEYCol.object_id = PKnUTable.object_id
AND PKnUKEYCol.column_id = PKnUColIdx.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=PKnUTable.name
AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name
UNION ALL
SELECT CONSTRAINT_NAME= CAST (oConstraint.name AS VARCHAR(30)) ,
CONSTRAINT_TYPE='FK',
PARENT_TABLE_NAME=CAST (oParent.name AS VARCHAR(30)) ,
PARENT_COL_NAME=CAST ( oParentCol.name AS VARCHAR(30)) ,
PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,
REFERENCE_TABLE_NAME=CAST ( oReference.name AS VARCHAR(30)) ,
REFERENCE_COL_NAME=CAST (oReferenceCol.name AS VARCHAR(30))
FROM sys.foreign_key_columns FKC
INNER JOIN sys.sysobjects oConstraint
ON FKC.constraint_object_id=oConstraint.id
INNER JOIN sys.sysobjects oParent
ON FKC.parent_object_id=oParent.id
INNER JOIN sys.all_columns oParentCol
ON FKC.parent_object_id=oParentCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.parent_column_id=oParentCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
INNER JOIN sys.sysobjects oReference
ON FKC.referenced_object_id=oReference.id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=oParent.name
AND oParentColDtl.COLUMN_NAME=oParentCol.name
INNER JOIN sys.all_columns oReferenceCol
ON FKC.referenced_object_id=oReferenceCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.referenced_column_id=oReferenceCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
)
select * from ALL_KEYS_IN_TABLE
where
PARENT_TABLE_NAME in ('YOUR_TABLE_NAME')
or REFERENCE_TABLE_NAME in ('YOUR_TABLE_NAME')
ORDER BY PARENT_TABLE_NAME,CONSTRAINT_NAME;
pour référence veuillez lire thru - http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx
Merci Guy.
Avec une légère variation, je l'ai utilisé pour trouver toutes les clés primaires pour toutes les tables.
SELECT A.Name,Col.Column_Name from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col ,
(select NAME from dbo.sysobjects where xtype='u') AS A
WHERE
Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY '
AND Col.Table_Name = A.Name
SELECT A.TABLE_NAME as [Table_name], A.CONSTRAINT_NAME as [Primary_Key]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
celui-ci vous donne les colonnes qui sont PK.
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'TableName'
le système stocké procédure sp_help vous donnera les informations. Exécutez l'instruction suivante:
execute sp_help table_name
j'ai trouvé cela utile, donne une liste de tableaux avec une virgule de la liste séparée des colonnes et puis aussi une virgule de la liste séparée de ceux qui sont la clé primaire
SELECT T.TABLE_SCHEMA, T.TABLE_NAME,
STUFF((
SELECT ', ' + C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND T.TABLE_NAME = C.TABLE_NAME
FOR XML PATH ('')
), 1, 2, '') AS Columns,
STUFF((
SELECT ', ' + C.COLUMN_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON C.TABLE_SCHEMA = TC.TABLE_SCHEMA
AND C.TABLE_NAME = TC.TABLE_NAME
WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND T.TABLE_NAME = C.TABLE_NAME
AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
FOR XML PATH ('')
), 1, 2, '') AS [Key]
FROM INFORMATION_SCHEMA.TABLES T
ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME
ci-dessous la requête listera clés primaires de table particulière :
SELECT DISTINCT
CONSTRAINT_NAME AS [Constraint],
TABLE_SCHEMA AS [Schema],
TABLE_NAME AS TableName
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_NAME = 'mytablename'
je dis une simple Technique que je suis
SP_HELP 'table_name'
exécutez ce code comme requête. Mentionnez votre nom de table au lieu de table_name pour lequel vous voulez connaître la clé primaire (n'oubliez pas les guillemets simples). Le résultat s'affichera comme Image jointe. Espérons, vous aidera à
SELECT t.name AS 'table', i.name AS 'index', it.xtype,
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 1
AND k.id = t.id)
AS 'column1',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 2
AND k.id = t.id)
AS 'column2',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 3
AND k.id = t.id)
AS 'column3',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 4
AND k.id = t.id)
AS 'column4',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 5
AND k.id = t.id)
AS 'column5',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 6
AND k.id = t.id)
AS 'column6',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 7
AND k.id = t.id)
AS 'column7',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 8
AND k.id = t.id)
AS 'column8',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 9
AND k.id = t.id)
AS 'column9',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 10
AND k.id = t.id)
AS 'column10',
FROM sysobjects t
INNER JOIN sysindexes i ON i.id = t.id
INNER JOIN sysobjects it ON it.parent_obj = t.id AND it.name = i.name
WHERE it.xtype = 'PK'
ORDER BY t.name, i.name
essayez ceci:
SELECT
CONSTRAINT_CATALOG AS DataBaseName,
CONSTRAINT_SCHEMA AS SchemaName,
TABLE_NAME AS TableName,
CONSTRAINT_Name AS PrimaryKey
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'Primary Key' and Table_Name = 'YourTable'
cette version affiche le schéma, le nom de la table et une liste de touches primaires séparées par des virgules. Object_Id () ne fonctionne pas pour les serveurs de liens donc nous filtrons par le nom de la table.
sans le remplacement(Si1.Column_Name,",") il montrerait les étiquettes d'ouverture et de fermeture xml pour Column_Name sur la base de données que je testais sur. Je ne suis pas sûr pourquoi la base de données a exigé un remplacement pour 'Column_Name' donc si quelqu'un sait alors s'il vous plaît commenter.
DECLARE @TableName VARCHAR(100) = '';
WITH Sysinfo
AS (SELECT Kcu.Table_Name
, Kcu.Table_Schema AS Schema_Name
, Kcu.Column_Name
, Kcu.Ordinal_Position
FROM [LinkServer].Information_Schema.Key_Column_Usage Kcu
JOIN [LinkServer].Information_Schema.Table_Constraints AS Tc ON Tc.Constraint_Name = Kcu.Constraint_Name
WHERE Tc.Constraint_Type = 'Primary Key')
SELECT Schema_Name
,Table_Name
, STUFF(
(
SELECT ', '
, REPLACE(Si1.Column_Name, '', '')
FROM Sysinfo Si1
WHERE Si1.Table_Name = Si2.Table_Name
ORDER BY Si1.Table_Name
, Si1.Ordinal_Position
FOR XML PATH('')
), 1, 2, '') AS Primary_Keys
FROM Sysinfo Si2
WHERE Table_Name = CASE
WHEN @TableName NOT IN( '', 'All')
THEN @TableName
ELSE Table_Name
END
GROUP BY Si2.Table_Name, Si2.Schema_Name;
Et le même schéma à l'aide de George de la requête:
DECLARE @TableName VARCHAR(100) = '';
WITH Sysinfo
AS (SELECT S.Name AS Schema_Name
, T.Name AS Table_Name
, Tc.Name AS Column_Name
, Ic.Key_Ordinal AS Ordinal_Position
FROM [LinkServer].Sys.Schemas S
JOIN [LinkServer].Sys.Tables T ON S.Schema_Id = T.Schema_Id
JOIN [LinkServer].Sys.Indexes I ON T.Object_Id = I.Object_Id
JOIN [LinkServer].Sys.Index_Columns Ic ON I.Object_Id = Ic.Object_Id
AND I.Index_Id = Ic.Index_Id
JOIN [LinkServer].Sys.Columns Tc ON Ic.Object_Id = Tc.Object_Id
AND Ic.Column_Id = Tc.Column_Id
WHERE I.Is_Primary_Key = 1)
SELECT Schema_Name
,Table_Name
, STUFF(
(
SELECT ', '
, REPLACE(Si1.Column_Name, '', '')
FROM Sysinfo Si1
WHERE Si1.Table_Name = Si2.Table_Name
ORDER BY Si1.Table_Name
, Si1.Ordinal_Position
FOR XML PATH('')
), 1, 2, '') AS Primary_Keys
FROM Sysinfo Si2
WHERE Table_Name = CASE
WHEN @TableName NOT IN('', 'All')
THEN @TableName
ELSE Table_Name
END
GROUP BY Si2.Table_Name, Si2.Schema_Name;
Sys.La Table objets contient la ligne pour chaque utilisateur défini, schema-scoped objet.
les contraintes créées comme la clé primaire ou d'autres seront l'objet et Nom de la Table sera le parent_object
Requête de sys.Objets et de recueillir les ID de L'objet de Type requis
declare @TableName nvarchar(50)='TblInvoice' -- your table name
declare @TypeOfKey nvarchar(50)='PK' -- For Primary key
SELECT Name FROM sys.objects
WHERE type = @TypeOfKey
AND parent_object_id = OBJECT_ID (@TableName)
puis-je Suggérer une réponse plus précise et simple à la question originale ci-dessous
SELECT
KEYS.table_schema, KEYS.table_name, KEYS.column_name, KEYS.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE keys
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS
ON cons.TABLE_SCHEMA = keys.TABLE_SCHEMA
AND cons.TABLE_NAME = keys.TABLE_NAME
AND cons.CONSTRAINT_NAME = keys.CONSTRAINT_NAME
WHERE cons.CONSTRAINT_TYPE = 'PRIMARY KEY'
Notes:
- certaines des réponses ci-dessus sont manquantes un filtre pour juste la clé primaire colonnes!
- j'utilise ci-dessous dans un CTE pour joindre à une plus grande colonne liste de fournir les métadonnées à partir d'une source d'alimentation BIML génération de mise en place des tables et SSIS code
pourrait être récemment posté, mais espérons que cela aidera quelqu'un à voir la liste des clés primaires dans le serveur sql en utilisant cette requête T-sql:
SELECT schema_name(t.schema_id) AS [schema_name], t.name AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS PrimaryKeyColumnName,
i.name AS PrimaryKeyConstraintName
FROM sys.tables t
INNER JOIN sys.indexes AS i on t.object_id=i.object_id
INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE OBJECT_NAME(ic.OBJECT_ID) = 'YourTableNameHere'
vous pouvez voir la liste de toutes les clés étrangères en utilisant cette requête si vous voulez:
SELECT
f.name as ForeignKeyConstraintName
,OBJECT_NAME(f.parent_object_id) AS ReferencingTableName
,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ReferencingColumnName
,OBJECT_NAME (f.referenced_object_id) AS ReferencedTableName
,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS
ReferencedColumnName ,delete_referential_action_desc AS
DeleteReferentialActionDesc ,update_referential_action_desc AS
UpdateReferentialActionDesc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
--WHERE OBJECT_NAME(f.parent_object_id) = 'YourTableNameHere'
--If you want to know referecing table details
WHERE OBJECT_NAME(f.referenced_object_id) = 'YourTableNameHere'
--If you want to know refereced table details
ORDER BY f.name
j'ai trouvé cela de mon ami, très efficace si vous êtes à la recherche de toutes les clés primaires de la table sous un schéma particulier.
SELECT tc.constraint_name AS IndexName,tc.table_name AS TableName,tc.table_schema
AS SchemaName,kc.column_name AS COLUMN_NAME
FROM information_schema.table_constraints tc,information_schema.key_column_usage kc
WHERE tc.constraint_type = 'PRIMARY KEY' AND kc.table_name = tc.table_name AND kc.table_schema = tc.table_schema
AND kc.constraint_name = tc.constraint_name AND tc.table_schema='<SCHEMA_NAME>'
si vous cherchez à faire votre propre ORM ou de générer du code à partir d'une table donnée, alors ce pourrait être ce que vous cherchez forme:
declare @table varchar(100) = 'mytable';
with cte as
(
select
tc.CONSTRAINT_SCHEMA
, tc.CONSTRAINT_TYPE
, tc.TABLE_NAME
, ccu.COLUMN_NAME
, IS_NULLABLE
, DATA_TYPE
, CHARACTER_MAXIMUM_LENGTH
, NUMERIC_PRECISION
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu on tc.TABLE_NAME=ccu.TABLE_NAME and tc.TABLE_SCHEMA=ccu.TABLE_SCHEMA
inner join information_schema.COLUMNS c on ccu.COLUMN_NAME=c.COLUMN_NAME and ccu.TABLE_NAME=c.TABLE_NAME and ccu.TABLE_SCHEMA=c.TABLE_SCHEMA
where
tc.table_name=@table
and
ccu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME
union
select TABLE_SCHEMA,'COLUMN', TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@table
and COLUMN_NAME not in (select COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = @table)
)
select
cast(iif(CONSTRAINT_TYPE='PRIMARY KEY',1,0) as bit) PrimaryKey
,cast(iif(CONSTRAINT_TYPE='FOREIGN KEY',1,0) as bit) ForeignKey
,cast(iif(CONSTRAINT_TYPE='COLUMN',1,0) as bit) NotKey
,COLUMN_NAME
,cast(iif(is_nullable='NO',0,1) as bit) IsNullable
, DATA_TYPE
, CHARACTER_MAXIMUM_LENGTH
, NUMERIC_PRECISION
from
cte
order by
case CONSTRAINT_TYPE
when 'PRIMARY KEY' then 1
when 'FOREIGN KEY' then 2
else 3 end
, COLUMN_NAME
voici à quoi ressemblerait le résultat:
<table cellspacing=0 border=1>
<tr>
<td style=min-width:50px>PrimaryKey</td>
<td style=min-width:50px>ForeignKey</td>
<td style=min-width:50px>NotKey</td>
<td style=min-width:50px>COLUMN_NAME</td>
<td style=min-width:50px>IsNullable</td>
<td style=min-width:50px>DATA_TYPE</td>
<td style=min-width:50px>CHARACTER_MAXIMUM_LENGTH</td>
<td style=min-width:50px>NUMERIC_PRECISION</td>
</tr>
<tr>
<td style=min-width:50px>1</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>LectureNoteID</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>int</td>
<td style=min-width:50px>NULL</td>
<td style=min-width:50px>10</td>
</tr>
<tr>
<td style=min-width:50px>0</td>
<td style=min-width:50px>1</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>LectureId</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>int</td>
<td style=min-width:50px>NULL</td>
<td style=min-width:50px>10</td>
</tr>
<tr>
<td style=min-width:50px>0</td>
<td style=min-width:50px>1</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>NoteTypeID</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>int</td>
<td style=min-width:50px>NULL</td>
<td style=min-width:50px>10</td>
</tr>
<tr>
<td style=min-width:50px>0</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>1</td>
<td style=min-width:50px>Body</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>nvarchar</td>
<td style=min-width:50px>-1</td>
<td style=min-width:50px>NULL</td>
</tr>
<tr>
<td style=min-width:50px>0</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>1</td>
<td style=min-width:50px>DisplayOrder</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>int</td>
<td style=min-width:50px>NULL</td>
<td style=min-width:50px>10</td>
</tr>
</table>
si la clé primaire et le type sont nécessaires, cette requête peut être utile:
SELECT L.TABLE_SCHEMA, L.TABLE_NAME, L.COLUMN_NAME, R.TypeName
FROM(
SELECT COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
)L
LEFT JOIN (
SELECT
OBJECT_NAME(c.OBJECT_ID) TableName ,c.name AS ColumnName ,t.name AS TypeName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
)R ON L.COLUMN_NAME = R.ColumnName AND L.TABLE_NAME = R.TableName