CODEWITHSUNDEEP
sql-serverinformation-schema
AMP
AMP

Reputation: 1

SQL Query to list all tables+columns in a specific database/server?

I have lots of servers ranging from ABC001 to ABC100, and each one has 100+ companies under it, each company with 100+ tables under it. I want to select a specific server (i.e. ABC005) to get the list of all tables from. I don't wanna get that list for literally everything from ABC001 to ABC100.

I know I can pull it with

Select * 
FROM INFORMATION_SCHEMA.COLUMNS

But is there a way to point to a specific server/DB so that I can save resources and time?

Should it be FROM ABC005.dbo.INFORMATION_SCHEMA.COLUMNS for example?

Upvotes: 0

Views: 220

Answers (1)

James L.
James L.

Reputation: 9453

Not sure how to hit a specific server. Adding the DB name before ABC005.INFORMATION_SCHEMA.COLUMNS (no .dbo.) works. But I find the data returned from the following query to be a bit easier to read:

select t.name
      ,c.name
      ,st.name as system_type
      ,c.max_length
      ,c.precision
      ,c.scale
      ,d.definition
      ,c.is_nullable
      ,c.is_rowguidcol
      ,c.is_filestream
from   ABC005.sys.tables t 
       inner join ABC005.sys.columns c on t.object_id = c.object_id
       inner join ABC005.sys.types st on c.system_type_id = st.user_type_id
       left outer join ABC005.sys.default_constraints d on c.default_object_id = d.object_id
where  t.type = 'U'
order by t.name, c.column_id

Upvotes: 1

Related Questions