sys
In Microsoft SQL Server, the sys schema is a special schema that contains system views and stored procedures that provide metadata about the SQL Server database. These system views and stored procedures offer information about database objects, configurations, and other system-level data. Here’s a more detailed explanation:
Key Features of the sys Schema
- System Views: The
sysschema includes a wide range of system views that provide detailed information about the database and its objects. Examples of these views include:sys.tables: Contains a row for each table object in the database.sys.columns: Contains a row for each column in each table or view.sys.indexes: Contains a row for each index and table in the database.sys.objects: Contains a row for each object that is a user-defined table, view, stored procedure, function, or other user-defined object.
- System Stored Procedures: The
sysschema also includes system stored procedures that perform various administrative tasks. Examples include:sp_help: Provides information about a database object.sp_rename: Renames a database object.sp_columns: Returns column information for a specified table or view.
- Metadata Access: The views in the
sysschema allow you to query metadata, which can be useful for understanding the structure and configuration of your databases. This metadata includes information about tables, columns, indexes, constraints, stored procedures, and more. - Security: Access to the
sysschema is typically restricted to users with appropriate permissions. Database administrators and developers with higher privilege levels can query these views to gather necessary information for database management and optimization.
The sp_rename stored procedure in Microsoft SQL Server is used to rename database objects such as tables, columns, indexes, or user-defined types. Here are some examples of how to use sp_rename:
EXEC sp_rename 'OldTableName', 'NewTableName';
EXEC sp_rename 'TableName.OldColumnName', 'NewColumnName', 'COLUMN';
EXEC sp_rename 'TableName.OldIndexName', 'NewIndexName', 'INDEX';
EXEC sp_rename 'OldTypeName', 'NewTypeName', 'USERDATATYPE';
The sp_columns stored procedure in Microsoft SQL Server is used to return information about the columns in a specified table or view. This information includes column names, data types, precision, scale, length, and other attributes. Here’s a detailed look at how to use sp_columns, along with some examples.
sp_columns [ @table_name = ] 'table_name'
[ , [ @table_owner = ] 'table_owner' ]
[ , [ @table_qualifier = ] 'table_qualifier' ]
[ , [ @column_name = ] 'column_name' ]Parameters
@table_name: The name of the table or view for which column information is being requested.@table_owner: The owner of the table. This is optional and can be used to specify the schema.@table_qualifier: The name of the database. This is optional and can be used to specify the database.@column_name: The name of the column for which information is being requested. This is optional and can be used to filter the results to a specific column.
EXEC sp_columns @table_name = 'Employee';
EXEC sp_columns @table_name = 'Employee', @table_owner = 'HR';
EXEC sp_columns @table_name = 'Employee', @column_name = 'EmployeeID';
--Get Columns of a Table in a Specific Database
EXEC sp_columns @table_name = 'Employee', @table_qualifier = 'CompanyDB';
The sp_help stored procedure in Microsoft SQL Server provides detailed information about a database object, such as a table, view, stored procedure, user-defined type, or any other object. This information includes column definitions, indexes, constraints, and other relevant details.
--Get Information About a Table
EXEC sp_help 'Employee';
--Get Information About a View
EXEC sp_help 'EmployeeView';
--Get Information About a Stored Procedure
EXEC sp_help 'usp_GetEmployeeDetails';
--Get Information About a Stored Procedure
EXEC sp_help 'usp_GetEmployeeDetails';
-- Get Information About All Objects in the Database
EXEC sp_help;--Get All column information of table
SELECT C.* FROM sys.tables T
INNER JOIN sys.columns C ON C.object_id = T.object_id
WHERE T.name = 'TableName'INFORMATION_SCHEMA
INFORMATION_SCHEMA is a standardized set of read-only views provided by SQL Server (and other relational database management systems) that offer a way to access metadata about the database objects.
Key Features of INFORMATION_SCHEMA
- Standardized Access:
INFORMATION_SCHEMAviews are part of the SQL-92 standard, meaning they provide a consistent way to access metadata across different database systems. - Read-Only: These views are read-only, which ensures that querying them does not modify any data or database objects.
- Comprehensive Metadata: The views in the
INFORMATION_SCHEMAschema cover a wide range of metadata, including information about tables, columns, views, constraints, indexes, and more.
Common INFORMATION_SCHEMA Views
Here are some of the most commonly used views within the INFORMATION_SCHEMA schema:
- INFORMATION_SCHEMA.TABLES: Provides information about all tables and views in the database.
- INFORMATION_SCHEMA.COLUMNS: Provides information about columns in tables and views.
- INFORMATION_SCHEMA.TABLE_CONSTRAINTS: Provides information about table constraints, such as primary keys and foreign keys.
- INFORMATION_SCHEMA.KEY_COLUMN_USAGE: Provides information about which columns are constrained by keys.
- INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE: Provides information about columns used in constraints.
- INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS: Provides information about foreign key constraints.
- INFORMATION_SCHEMA.SCHEMATA: Provides information about schemas in the database.
- INFORMATION_SCHEMA.VIEWS: Provides information about views in the database.
OBJECT_ID(): Always find objectID of any object by using OBJECT_ID()--How to get Temporary table(#Tablename) or Global table(##Tablename)
SELECT
t.name,*
FROM
tempdb.sys.columns c
INNER JOIN
tempdb.sys.types t ON c.user_type_id = t.user_type_id
WHERE
c.object_id = OBJECT_ID('tempdb..#demotable');SELECT * FROM sys.types
-- return datatype table of sql server