FIND ALL MY TABLES SQL TABS CODE

This version allows you to have it in an administrative database and then search through any database. It's a bit more advanced now but looks a lot messier (but it's in a proc so you'll never see it) and it's formatted better. I've re-formatted the proc a bit since I posted this. For 2008r2 and earlier use + to concatenate strings. It is worth noting that the concat feature was added in 2012. SELECT CONCAT(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) AS, name AS FROM sys.columns Scan through all tables to identify all tables with columns that have the provided string Note you can also create a function based on what I have: CREATE PROCEDURE usp_tablecheck

FIND ALL MY TABLES SQL TABS CODE

If you want the Schema Name as well (which in a lot of cases you will, as you'll have a lot of different schemas, and unless you can remember every table in the database and where it belongs this can be useful) run: select OBJECT_SCHEMA_NAME(object_id),object_name(object_id) from sys.columnsĪnd finally if you want it in a nicer format (although this is where the code (In my opinion) is getting too complicated for easy writing): select concat(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) from sys.columns

If you simply want the table name you can run: select object_name(object_id) from sys.columns Alternately you could search for just tables by using T.Name. You could add other conditionals, I also added the columns in the select clause in this vertical manner so it was easy to reorder, remove, rename, or add others based on your needs. For example, if you only wanted to see the fields which must have values add and c.is_nullable = 0 This here will give you a little extra information about the schema, tables and columns that you may or may not choose to use extra conditions in your where clause to filter on.

Find all my tables sql tabs