MS Access query

[Walt_Lukasik1]

Hey gang,

This access query goes against the Onbase sql server system tables and let's you list all the columns in a particular table or all the columns containing a column name.  Partial entries are allowed as the query uses wildcards if the entry is not spelled out in full.  This works with tables and colums or one or the other.  Here is the script:

SELECT sys_all_objects.name, sys_all_columns.name, sys_all_columns.max_length FROM sys_all_columns INNER JOIN sys_all_objects ON sys_all_columns.object_id = sys_all_objects.object_id WHERE (((sys_all_objects.type)="u")) GROUP BY sys_all_objects.name, sys_all_columns.name, sys_all_columns.max_length HAVING (((sys_all_objects.name) Like "*" & [Enter Table Name] & "*") AND ((sys_all_columns.name) Like "*" & [Enter Column Name] & "*")) ORDER BY sys_all_objects.name, sys_all_columns.name;

[lboening]

Is this a question? Or just handy tip, something for a wiki!

[Michael_Morin]

Same query, but rewritten for execution in SQL Management Studio (ad hoc text queries), and with additional column for data type.  Remove the table name or column name from the where clause for wide-open query.

select
 o.name as TableName
 ,c.name as ColumnName
 ,t.name as DataType
 ,c.max_length as MaxDataLength
from sys.columns c
inner join sys.objects o on c.object_id = o.object_id
inner join sys.types t on t.system_type_id = c.system_type_id
where 1=1
 and o.type = 'U'
 and o.name like '%action%'  --enter the desired table name between the %'s
 and c.name like '%actionname%' --enter the desired column name between the %'s
order by o.name, c.name

[Ansley_Ingram]

Hi Mike and Walt,

Thanks for sharing your queries. These will be useful for customers that are exploring the schema.

Ansley