MS-SQL - inspection structure BD
/SQL/MS-SQL - inspection structure BD.sql
-- Recherche tables :
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
(ou)
SELECT * FROM dbo.sysobjects WHERE xtype= 'U' ORDER BY name
'P' (pour procédures)
'FN' (pour fonctions)
-- Recherche colonnes :
SELECT * FROM information_schema.columns WHERE column_name = 'vivelesbananes'
-- Info sur une table :
sp_columns @table_name='dossier'
-- FK Target/Source table :
SELECT CCU.constraint_name src_constraint, CCU.table_name src_table, CCU.column_name src_col, KCU.table_name target_table, KCU.column_name target_col
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON (CCU.constraint_name = RC.constraint_name)
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON (KCU.constraint_name = RC.unique_constraint_name)
where 1=1
-- and CCU.table_name = '' -- src_table
and KCU.table_name = 'lieu_hebergement' -- target_table
-- and CCU.constraint_name = '' -- constraint_name
ORDER BY CCU.table_name, CCU.constraint_name