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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy