Como saber que columnas no tienen valor por defecto

Hola Pablo:

Pablo Roca ha supuesto :

Hola,

Queria saber que columnas de mi base de datos no tienen definido un valor por
defecto

Hago una primera aproximación con esta select

SELECT * FROM information_schema.columns WHERE column_default IS NULL
AND table_name <> 'sysdiagrams'
AND table_name IN(SELECT table_name FROM information_schema.tables WHERE
table_type = 'BASE TABLE')
AND table_name+column_name NOT IN(SELECT table_name+column_name FROM
information_schema.constraint_column_usage)

aqui elimino sysdiagrams, elimino las vistas y los campos primary key y
foreign key

pero me salen los campos calculados.

¿Como quitar los campos calculados de esta selección?

Utiliza sys.computed_columns para excluir las columnas calculadas

Un saludo

http://www.moondance.es

Hola Juan Diego,

Efectivamente ahi está, me dieron una pista con sys.columns, pero lo que
dices es mejor, gracias.

Bien, pues así queda la SELECT por si al alguien mas le puede interesar:


USE mibasededatos

SELECT * FROM information_schema.columns AS SC WHERE column_default IS NULL
AND table_name <> 'sysdiagrams'
AND table_name IN(SELECT table_name FROM information_schema.tables WHERE
table_type = 'BASE TABLE')
AND table_name+column_name NOT IN(SELECT table_name+column_name FROM
information_schema.constraint_column_usage)
AND SC.table_name+SC.column_name NOT IN(SELECT object_name(object_id)+name
FROM sys.computed_columns)



Saludos,

Pablo Roca
La Coruna - Spain
http://www.portalfox.com

Hola Pablo,

On Fri, 23 May 2008 11:23:39 +0200, "Pablo Roca"
wrote:

Queria saber que columnas de mi base de datos no tienen definido un valor
por defecto

A ver si te vale esto:

SELECT object_name(t.object_id) as TableName, c.name as ColumnName
FROM sys.columns c join sys.tables t on c.object_id = t.object_id
where default_object_id = 0 and is_computed=0


Saludos
Alfredo

Hola Alfredo,

Ya le respondí a Juan Diego con una version que me funciona.

En esta tuya no estas excluyendo las PKs ni las FKs

Pero con esta adaptación tambien va perfectamente:

SELECT object_name(t.object_id) as TableName, c.name as ColumnName
FROM sys.columns c join sys.tables t on c.object_id = t.object_id
where default_object_id = 0 and is_computed=0
AND object_name(t.object_id)+c.name NOT IN(SELECT table_name+column_name
FROM information_schema.constraint_column_usage)
AND object_name(t.object_id) <> 'sysdiagrams'

Gracias.


Saludos,

Pablo Roca
La Coruna - Spain
http://www.portalfox.com
"Alfredo Novoa" escribió en el mensaje
news:

Hola Pablo,

On Fri, 23 May 2008 11:23:39 +0200, "Pablo Roca"
wrote:

Queria saber que columnas de mi base de datos no tienen definido un valor
por defecto

A ver si te vale esto:

SELECT object_name(t.object_id) as TableName, c.name as ColumnName
FROM sys.columns c join sys.tables t on c.object_id = t.object_id
where default_object_id = 0 and is_computed=0


Saludos
Alfredo

Leche! :))

Corregido para poner mejor esos IN

SELECT table_name AS tabla, column_name as columna, ordinal_position AS
posicion
FROM information_schema.columns AS SC WHERE column_default IS NULL
AND (EXISTS(
SELECT * FROM information_schema.tables AS T
WHERE (T.table_type = 'BASE TABLE')
AND (T.table_name = SC.table_name)
AND (T.table_name <> 'sysdiagrams')
))
AND (NOT EXISTS(
SELECT *
FROM information_schema.constraint_column_usage CU
WHERE (CU.table_name = SC.table_name)
AND (CU.column_name = SC.column_name)
))
AND (NOT EXISTS(
SELECT * FROM sys.computed_columns AS C
WHERE (object_name(C.object_id) = SC.table_name)
AND (C.name = SC.column_name)
))
ORDER BY table_name, ordinal_position


Saludos,

Pablo Roca
La Coruna - Spain
http://www.portalfox.com