09-11-2012 01:35 PM
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;
12-13-2012 12:34 PM
Is this a question? Or just handy tip, something for a wiki!
12-19-2012 09:56 AM
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
01-11-2013 07:38 AM
Hi Mike and Walt,
Thanks for sharing your queries. These will be useful for customers that are exploring the schema.
Ansley
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.