Find me a column...

Forgive the fact that the following SQL is not formatted, but this topic came up today on a list that I frequent, which was how to get all the fieldnames of a view (or table etc). this dandy little select statement will gather just that and all you need to really parse it for is your table name or column name... maybe you want to know how many times you named something "EntryTime" this is an easy way to find all that information at a glance.

This is a Sql Server 2005 version (If you need it I can post a Sql Server 2000 version as well.)

SELECT t.name AS TableName,
sc.name AS SchemaName,
c.name AS ColumnName,
c.column_id AS ColumnID,
c.precision AS [Precision],
types.name AS TypeName,
basetypes.name AS BaseTypeName,
st.name AS TypeSchemaName,
CASE WHEN c.max_length>=0 AND basetypes.name IN (N'nchar', N'nvarchar') THEN c.max_length/2 ELSE c.max_length END AS Length,
c.scale AS Scale,
CONVERT(bit, c.is_identity) AS [Identity],
CONVERT(bit, c.is_computed) AS [IsComputed],
CONVERT(bit, ISNULL(ic.is_not_for_replication,0)) AS NotForReplication,
CAST(ISNULL(ic.seed_value,0) AS DECIMAL(38)) AS IdentitySeed,
CAST(ISNULL(ic.last_value,0) AS DECIMAL(38)) AS IdentityCurrent,
ISNULL(ic.increment_value,0) AS IdentityIncrement,
CONVERT(bit, c.is_nullable) AS AllowNulls,
c.default_object_id AS DefaultTextID,
ds.name AS DefaultName,
c.default_object_id AS defaultid,
dsc.name AS DefaultSchemaName,
CONVERT(bit, CASE ds.parent_object_id WHEN 0 THEN 1 ELSE 0 END) AS IsBoundDefault,
rs.name AS RuleName,
rsc.name AS RuleSchemaName,
CONVERT(bit, CASE WHEN fi.language_id IS NULL THEN 0 ELSE 1 END) AS FullTextIndexed,
cc.definition AS ComputedText,
c.is_rowguidcol AS IsRowGuidCol,
c.collation_name AS Collation,
fi.language_id AS FullTextLanguage,
fisc.name AS FullTextTypeColumn,
c.is_xml_document AS XMLDocument,
xscs.name AS XMLCollection,
xsc.name AS XMLCollectionSchemaName,
t.type AS type,
CONVERT(bit, ISNULL(cc.is_persisted, 0)) AS [IsPersisted]
FROM sys.all_columns c INNER JOIN sys.all_objects t WITH (NOLOCK) ON c.object_id=t.object_id
LEFT JOIN sys.schemas sc WITH (NOLOCK) ON t.schema_id=sc.schema_id
LEFT JOIN sys.types basetypes WITH (NOLOCK) ON c.system_type_id=basetypes.system_type_id AND basetypes.system_type_id=basetypes.user_type_id
LEFT JOIN sys.types types WITH (NOLOCK) ON c.user_type_id=types.user_type_id
LEFT JOIN sys.schemas st WITH (NOLOCK) ON st.schema_id=types.schema_id
LEFT JOIN sys.identity_columns ic WITH (NOLOCK) ON ic.object_id=c.object_id AND ic.column_id=c.column_id
LEFT JOIN sys.all_objects ds WITH (NOLOCK) ON ds.object_id=c.default_object_id
LEFT JOIN sys.all_objects rs WITH (NOLOCK) ON rs.object_id=c.rule_object_id
LEFT JOIN sys.fulltext_index_columns fi WITH (NOLOCK) ON fi.object_id=c.object_id AND fi.column_id=c.column_id
LEFT JOIN sys.all_columns fisc WITH (NOLOCK) ON fi.type_column_id = fisc.column_id AND fisc.object_id=c.object_id
LEFT JOIN sys.computed_columns cc WITH (NOLOCK) ON cc.object_id=c.object_id AND cc.column_id=c.column_id
LEFT JOIN sys.schemas dsc WITH (NOLOCK) ON dsc.schema_id=ds.schema_id
LEFT JOIN sys.schemas rsc WITH (NOLOCK) ON rsc.schema_id=rs.schema_id
LEFT JOIN sys.xml_schema_collections xscs WITH (NOLOCK) ON xscs.xml_collection_id=c.xml_collection_id
LEFT JOIN sys.schemas xsc WITH (NOLOCK) ON xsc.schema_id=xscs.schema_id
WHERE t.type IN ('U', 'FT', 'TF', 'IF', 'S', 'TT', 'V')
ORDER BY c.object_id, c.column_id