Useful DataBase MetaData Scripts

1. given a column name, return a list of tables with similar columns names

  declare @targetColumnName varchar(500)
  set @targetColumnName = '%YourTargetColumnNameHere%'

  select so.[name] as TableName, sc.[name] as ColumnName  
  from sysobjects so
  inner join syscolumns sc on (so.[id] = sc.[id])
  where (so.[type] = 'U') and (sc.[name] like @targetColumnName)

2. given a table name, return a list of tables with similar name

  declare @targetTableName varchar(500)
  set @targetTableName = '%Entity%'

  select so.[name] as TableName
  from sysobjects so
  where (so.[type] = 'U') and (so.[name] like @targetTableName)