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

Question

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?

It should work across databases but not across instances unless you have the respective linked servers. — sticky bit
– sticky bit, Commented Jul 10, 2020 at 9:40

James L. · Accepted Answer · 2020-07-10 19:13:19Z

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

If the servers are numbered sequentially, or if you have a list of servers in a separate file, I would write the sql in a separate file and would write a batchfile around it. If they are sequential you'd just loop around echo'ing the results to a text file.
@drHodge - Good suggestion, using SQLCMD and specifying the server name on the command line.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related