SQLTeam.com | Weblogs | Forums

Count the number of times a field appears in database


#1

Can someone direct me, or maybe has code that will count the number of times a field is in a database, for example CustomerID appears 2 times once in customer and account tables.


#2

See if this helps:

SELECT 
    DB_NAME() AS DatabaseName, T.[name] AS Table_Name, AC.[name] AS Column_Name, 
    AC.column_id AS Ordinal_Position, TY.[name] AS Data_type, 
    CASE WHEN AC.[precision] = 0 THEN AC.max_length ELSE AC.[precision] END AS CLength, 
    AC.is_nullable AS IsNullable, 
    OBJECT_DEFINITION(AC.default_object_id) AS Column_Default 
FROM sys.tables AS T 
INNER JOIN sys.all_columns AC ON T.[object_id] = AC.[object_id] 
INNER JOIN sys.types TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id] 
WHERE T.[is_ms_shipped] = 0;  

#3

Thank You, that's exactly what I needed.