My question is pretty much like the question here:
How to find column names for all tables in all databases in SQL Server
But I am not able to comment due to reputation not being 50 or > and I can't put anything in the answer box because you're supposed to put ANSWERS in the answer box.
I got this code from Stack Overflow:
SELECT *
FROM master..sysdatabases
where dbid > 4
--order by dbid
order by name
and it works fantastic - it lists all of our databases.
What I would like to do is get a result table that shows these columns:
DatabaseName, TableSPViewName, FieldName, TypeSize, Indexed
DatabaseName would be JUST the name of the database.
TableSPViewName would contain
the name of all tables in that database
the name of any Stored Procs in that database
the name of all Views in that database
FieldName would be a listing of all fields in that table (if it were a table or view)
TypeSize would be the type and size of that field (like: int, varchar(##), bit ...)
EDIT SEPT 20, 2013 ---------------
ON THIS SITE:
http://blog.sqlauthority.com/2009/04/26/sql-server-list-all-the-tables-for-all-databases-using-system-tables/
I FOUND THIS:
exec sp_msforeachdb 'select "?" AS db, * from ?.sys.tables'
But that SP puts puts up a separate query window for each DB with all its tables so you get
DB with all its tables
DB with all its tables
DB with all its tables
DB with all its tables
I need:
DB 1 table 1 field 1
DB 1 table 1 field 2
DB 1 table 1 field 3
DB 1 table 2 field 1
DB 1 table 2 field 2
DB 1 table 2 field 3
DB 1 table 3 field 1
DB 1 table 3 field 2
DB 1 table 3 field 3
DB 2 table 1 field 1
DB 2 table 1 field 2
DB 2 table 1 field 3
DB 2 table 2 field 1
DB 2 table 2 field 2
DB 2 table 2 field 3
DB 2 table 3 field 1
DB 2 table 3 field 2
DB 2 table 3 field 3
EDIT #2 SEPT 20, 2013 ---------------
ON THIS SITE:
http://blog.clicdata.com/2012/08/02/how-to-list-all-tables-and-columns-with-types-in-a-sql-server-database/
I FOUND THIS:
SELECT tTables.name AS table_name,
tCols.name AS column_name,
tTypes.name,
tTypes.max_length,
tTypes.precision,
tTypes.scale
FROM sys.tables AS tTables
INNER JOIN sys.columns AS tCols
ON tTables.OBJECT_ID = tCols.OBJECT_ID
JOIN sys.types AS tTypes
ON tCols.user_type_id = tTypes.user_type_id
ORDER BY tTables.name;
But that just works for the CURRENT database in use.
It gets me REALLY CLOSE to what I'm looking for but I still need to have one query that does what the above does but for ALL DATABASES we have. See my long list of "DB 1 table 1 field 1" shown above. If we can get the above query to tack on the DATABASENAME onto the left side of what the above produces AND do it for all DBs we'll be in business!
