There are many instances when we want to get a list of all the columns in a database (or) find a particular column in a database, not sure in which table it is!!! OR the case may be even we want to find a column whose prefix or suffix may be know to us.
Instead of manually searching for column, we can query the sql server system tables and find that information.
Here is the query, you can just run it and see the results by your self. Actually it is selecting data from three system catalogs; Schemas, Objects and Columns. You can also try out selecting data from them individually and see what they return, these are really useful.Here, in the where clause I have put condition to get data for User Tables (U) and Views (V).
SELECT S.name as SchemaName,O.name as ObjectName, O.type_desc as ObjectType,C.name
FROM sys.schemas S
INNER JOIN sys.objects O ON S.schema_id=O.schema_id
INNER JOIN sys.columns C ON O.object_id=C.object_id
WHERE O.type in('U','V')
ORDER BY S.name,O.type_desc, O.name,C.Name
And here is how you may be seeing the result.
{ Comments on this entry are closed }