sql server - SQL Information_Schema Syntax for finding servername, table and column details

Question

I am looking for SQL Information_Schema syntax to retrieve Server Name, Table Name, Column Name, View and all the details.

I also need to find which Table : Column has Image files.

Is there any specific syntax to help me out.

Answer 1

There are many different information_schema views. If you want to see what views are available then in SSMS object explorer you can navigate to databases > system databases > msdb > views > system views and scroll down to the information_schema. Since these are views you can just query them. From your question the ones you'll be interested in are

INFORMATION_SCHEMA.columns
INFORMATION_SCHEMA.tables
INFORMATION_SCHEMA.views

Here's an example query that lists servername and all columns

select @@servername, *
from INFORMATION_SCHEMA.columns

You can join the views and filter your data just like you would any query. Hope this helps.

select * 
from information_schema.tables t
join INFORMATION_SCHEMA.columns c
on t.table_name = c.table_name
where c.data_type = 'image'