July 9, 2013 at 11:28 pm
Comments posted to this topic are about the item Get all fields names for all tables in SQL
July 10, 2013 at 12:05 am
I use this script to extract all fields from all tables.
select so.[Name] 'Table', sc.ColID 'Seq', sc.[Name] 'Field'
FROM syscolumns sc
INNER JOIN sysobjects so ON so.[ID] = sc.[ID]
WHERE so.xtype in (
'IT' /*Internal table*/
,'U' /*User table */
,'V' /*View */
)
ORDER BY so.[Name], sc.ColID, sc.[Name]
I use it to populate an Excel spreadsheet where I put filters on 'Table' and 'Field'.
Filtering on a field name shows all tables with that field
Filtering using 'contains' also shows similarly named fields e.g. all fields containing 'Route'
I can also list all fields in a specific table without going to SSMS.
July 17, 2013 at 1:11 pm
For SQL 2005+, use the built in information schema views.
SELECT TABLE_CATALOG
, TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
, ORDINAL_POSITION
, COLUMN_DEFAULT
, IS_NULLABLE
, DATA_TYPE
, CHARACTER_MAXIMUM_LENGTH
, CHARACTER_OCTET_LENGTH
, NUMERIC_PRECISION
, NUMERIC_PRECISION_RADIX
, NUMERIC_SCALE
, DATETIME_PRECISION
, CHARACTER_SET_CATALOG
, CHARACTER_SET_SCHEMA
, CHARACTER_SET_NAME
, COLLATION_CATALOG
, COLLATION_SCHEMA
, COLLATION_NAME
, DOMAIN_CATALOG
, DOMAIN_SCHEMA
, DOMAIN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
Wes
(A solid design is always preferable to a creative workaround)
November 13, 2013 at 9:45 am
You can do the same thing with sp_help 'tablename'
May 2, 2016 at 1:03 pm
Thanks for the script.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply