This is commonly used by other developer to actually developed an auto generator tool for their architecture.
For Microsoft documentation, please visit this link.
See below how to query the SQL Server Objects from the SQL Server.
Querying Objects
Inside the [sys] schema, if your SQL Server has intellisense, you can see all the schema definition objects if you put a dot after [sys] keyword. See below the screenshot.
You can select what schema object you may query for. We can use a default SELECT query to do a query.
Let say for example, we can query the tables, views, stored procedures and functions with the use of sys.objects catalog.
Querying Tables
We can use the sys.objects catalog to query the list of tables from the database. See sample code below.
SELECT object_id
, name
, type
, type_desc
, create_date
, modify_date
FROM sys.objects
WHERE type = 'u'
ORDER BY name;Type 'U' defined the object as the USER_TABLE. If we want to query the custom stored procedures, then we can filter the type 'P'.
Querying Fields
We can use the sys.columns catalog to query all the columns from the database. After querying the columns, we can use the object_id field to determine what table it is belong to. See sample below.
SELECT object_id
, name
, column_id
, max_length
, user_type_id
, system_type_id
FROM sys.columns;
Joining the table and only querying the custom table columns.
SELECT o.object_id
, o.name
as tablename
, c.name
as columnname
, c.column_id
, c.max_length
, c.user_type_id
, c.system_type_id
FROM sys.columns c
INNER JOIN sys.objects o ON o.object_id = c.object_id
WHERE o.type = 'U'
ORDER BY o.name, c.name;
Same with the columns, we can use the sys.indexes to query the list of indexes under one table. See our sample below.
SELECT i.object_id
, o.name
as tablename
, i.name
, i.index_id
, i.type
, i.type_desc
, i.is_unique
, i.is_primary_key
FROM sys.indexes i
INNER JOIN sys.objects o ON o.object_id = i.object_id
WHERE o.type = 'u'
ORDER BY o.name;
Querying the indexed columns by table can be filtered using the sys.index_columns. See below.
SELECT i.object_id
, o.name
as tablename
, c.name
as columnname
, i.index_id
, i.index_column_id
, i.column_id
FROM sys.index_columns i
INNER JOIN sys.objects o ON o.object_id = i.object_id
INNER JOIN sys.columns c ON c.object_id = o.object_id AND c.column_id = i.column_id
WHERE o.type = 'u'
ORDER BY o.name;
Base in your requirements, you can expand and filter more specific objects inside [sys] schema.
No comments:
Post a Comment
Place your comments and ideas