[Code] Cómo obtener las columnas de una tabla identificando su llave primaria

Se presenta el script para obtener todos los campos de una tabla y saber cual(es) de ellos son llave primaria

declare @tabla sysname 
set @tabla = 'mitabla' --El nombre de la tabla que se quiere evaluar

;With PrimaryKeys (object_id, column_id) as (
SELECT ic.object_id, ic.column_id AS ColumnName FROM 
sys.indexes AS i 
INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id and ic.OBJECT_ID = OBJECT_ID(@tabla,N'U')
)
select name, case when PrimaryKeys.object_id is null then 0 else 1 end IsKey from sys.columns AS C
left join PrimaryKeys ON c.object_id = PrimaryKeys.object_id and c.column_id = PrimaryKeys.column_id
where c.OBJECT_ID = OBJECT_ID(@tabla,N'U')

Obsérvese que se usa la función OBJECT_ID para obtener el Id del Objeto que se quiere evaluar, otra opción sería:

OBJECT_NAME(ic.OBJECT_ID)  = @Tabla

pero es mas eficiente como se presenta en la consulta superior dado que sólo se ejecuta una vez la función escalar OBJECT_ID en lugar de una vez por cada registro, se observa también el uso de una instrucción CTE para calcular las llaves de la tabla

 

Los comentarios son bienvenidos, espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[CODE] Cómo concatenar los resultados de una consulta

Como se observa en el artículo [CODE] Cómo obtener la llave primaria de una tabla, es posible concatenar el resultado de una consulta en una variable, lo cual es especialmente útil en la generación de código dinámico:

declare @key varchar(8000)
set @key = ''
SELECT @key  = @key  + COALESCE(TABLE_NAME + ',' , '')  from INFORMATION_SCHEMA.TABLES

if len(@key) > 0 set @key = LEFT(@key,len(@key) - 1) --Eliminar la coma que sobra al final

print @key

Se debe tener en cuenta que existen algunas limintantes, por ejemplo se debe considerar el tamaño máximo de la variable, que dependiendo de la versión de SqlServer puede ser 8000 y usando varchar(max) puede ser máximo de 2,147,483,647 caracteres

Es de notar que usar funciones CLR o funciones de texto avanzadas de SqlServer 2012 puede mejorarse el rendimiento de esta solución

Los comentarios son bienvenidos, espero sea de ayuda,

 

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[CODE] Cómo obtener la llave primaria de una tabla

Obtener la llave primaria de una tabla

Normalmente y una de las primeras (además de ser la forma en que funciona para todas las versiones), dado que usa las consultas estándard INFORMATION_SCHEMA, es la que se presenta a continuación:

declare @Keys varchar(8000)

set @Keys = '' --Se estable a cadena vacía porque si deja en nulo el resultado final sería nulo

SELECT @Keys = @Keys + COALESCE(isc.COLUMN_NAME + ',' , '') FROM 
INFORMATION_SCHEMA.COLUMNS AS isc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
AS kcs ON isc.TABLE_NAME = kcs.TABLE_NAME AND isc.COLUMN_NAME = kcs.COLUMN_NAME 
AND LEFT(kcs.CONSTRAINT_NAME, 2) = 'PK'

print @Keys --Presenta las llaves separadas por coma

Otra consulta, en la que se usa la metadata de la base de datos es: (y la recomendada):

SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i 
INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id and i.is_primary_key = 1 

El uso de la primera o la segunda estará determinado por la versión de sql y qué tan estándard es la solución que se esté desarrollando

Espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP