Search Fields

[zezo2021]

if I have a big database with a lot of Tables and I have the field name

Can I search the field name in SSMS and tell which table exists?

Please provide me with the SQL CODE

Also, can this be found also in Access Search in tables by Field Name

 

[tvanstiphout]

ssms implies a SQL Server database. You should be able to query sys.columns for fields by that name. Something like:
select object_name(object_id) as theTable, name from sys.columns where name = '???';

 

[arnelgp]

you can use Query from within access, first create this function and call it in the query:

Public Function IsFieldNameInTable(ByVal tblName As String, ByVal Typ As Long, ByVal fldName As String) As Boolean
    Dim fld As DAO.Field
    If Left$(tblName, 4) = "Msys" Or Left$(tblName, 2) = "f_" Or (Typ <> 1 And Typ <> 4 And Typ <> 6) Then
        Exit Function
    End If
    With CurrentDb.OpenRecordset("select * from [" & tblName & "] where (0=1);", dbOpenSnapshot, dbReadOnly)
        For Each fld In .Fields
            If fld.Name = fldName Then
                IsFieldNameInTable = True
                Exit For
            End If
        Next
    End With
End Function

then create this query:

SELECT MsysObjects.name AS TableName 
FROM MsysObjects 
WHERE IsFieldNameInTable([NAME],[Type],"FullName")=True;

* note that "FullName" (on the sample query) is the fieldname you want to search.

 

[sonic8]

Can I search the field name in SSMS and tell which table exists?

SELECT TABLE_CATALOG
      ,TABLE_SCHEMA
      ,TABLE_NAME
      ,COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE COLUMN_NAME = 'YourKnownColumnName'

This works with a number of database systems, including Microsoft SQL Server, which support the INFORMATION_SCHEMA Standard.