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.
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;
1 Like
Thank You, that's exactly what I needed.