Helpful Sql Snippets for Investigating a New Project
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:
TABLE_QUALIFIER | TABLE_OWNER | TABLE_NAME | COLUMN_NAME | KEY_SEQ | PK_NAME |
---|---|---|---|---|---|
MyDatabase | MyDb | SalesHistory | SalesId | 1 | PK_SALESId |