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
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: