Thread by @apopoutsis

SQL Server Tip Thread: Using INFORMATION_SCHEMA

INFORMATION_SCHEMA lets you query tables, columns, and other metadata about your database. Knowing how to use it, and how to further leverage it to create SQL statements dynamically, can make you a more efficient SQL developer.

If you want to view this thread as an expanded blog post, you can also go check it out at:

apop.tech/posts/searchin…

I go into detail there about how I combined INFORMATION_SCHEMA with SQL cursors, dynamic SQL, and a temporary table to find data across 20+ different tables.

As a basic example, if you need to search across a large number of tables for one or more values, and the tables have a common pattern in their name, you can query INFORMATION_SCHEMA to find all of the tables you need to search.

This example searches across all tables in the CORE database to find tables that contain USER_CORE in the table name:

Here's the full Microsoft documentation on INFORMATION_SCHEMA for reference:

docs.microsoft.com/en-us/sql/rela…

If you found this thread helpful, please consider liking and retweeting the original tweet, and follow me for more content about SQL and data!

Excellent point here from @nilsandrey - INFORMATION_SCHEMA will only return metadata that your user has permission to access:

https://twitter.com/nilsandrey/status/1447962746511925256?s=20