When assigned a new full stack task on a new project, I usually have no clue what table or even what database holds the data I am tasked to work with.

But where is the data the user is asking for? If you the database is not clearly listed, these snippets have helped me find what I need.

First before I do anything crazy like search the entire database for a value, I will search the database for a column with a relevant name. If I am looking for sales figures I want to find all tables where a column has the word sales in it.

Find a Table Based on Column Name Containing a string

USE MyDbName
GO;

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like '%Sales%'
ORDER BY TABLE_NAME;

Now that I have found my table, I might need to start writing a new stored procedure or function that performs various operations on the data.

Now that I have the name of the table perhaps I need the technical object_id of the table for various operations.

Say by using the above snippet, I located the table I needed via searching for columns. The table I need is called SalesHistory.

Get the Object ID for a Table

I will now grab the object_id with the below snippet:

SELECT [NAME],OBJECT_ID,[TYPE] 
FROM SYS.OBJECTS 
WHERE [NAME] LIKE '%saleshistory%'

Next, I will check what views have been created. There may be some views that are relevant to the project.

USE MyDb
GO

SELECT 
  TABLE_SCHEMA,
  TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS;

Get a list of indexes and catalogs connected to a table’s object_id

Say I am working with full-text search. One thing I may need to do, now that I have the object id, is find out what index is connected to the table, and what catalog that index is inside.

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName, 
    c.name AS FTCatalogName ,
    f.name AS FileGroupName,
    i.name AS UniqueIdxName,
    cl.name AS ColumnName
FROM 
    sys.tables t 
INNER JOIN 
    sys.fulltext_indexes fi 
ON 
    t.[object_id] = fi.[object_id] 
INNER JOIN 
    sys.fulltext_index_columns ic
ON 
    ic.[object_id] = t.[object_id]
INNER JOIN
    sys.columns cl
ON 
    ic.column_id = cl.column_id
    AND ic.[object_id] = cl.[object_id]
INNER JOIN 
    sys.fulltext_catalogs c 
ON 
    fi.fulltext_catalog_id = c.fulltext_catalog_id
INNER JOIN 
    sys.filegroups f
ON
    fi.data_space_id = f.data_space_id
INNER JOIN 
    sys.indexes i
ON 
    fi.unique_index_id = i.index_id
    AND fi.[object_id] = i.[object_id];

If it turns out I am barking up the wrong tree and I need to get a list of all full text catalogs, I can simply search SQL Servers index of full text catalogs.

SELECT * FROM sys.fulltext_catalogs

Checking what column is the Primary Key

Often I need to check which column is the primary key for the database. I can do that by calling the built in sp_pkeys stored procedure.

If we know the type of the table

USE MyDatabase
GO

EXEC sp_pkeys @table_name = SalesHistory, @table_owner = MyDb;

It will output something like:

Change the following: