OPENXML, a Transact-SQL keyword, provides a rowset over in-memory XML documents that is similar to a table or a view. sp_xml_preparedocument. This parses the XML document and returns a handle to the parsed document that is ready for consumption. Example: Using nodes() Example: Using OpenXml() on the xml Data Type Using Cross Apply References: https://docs.microsoft.com/en-us/sql/relational-databases/xml/openxml-sql-server?view=sql-server-ver15
Sometimes we are not able to connect the server to execute a power shell script that time we can run the same power shell command using TSQL statement, To enable CMD Shell To get process list on server To get disk space from server To get performance counter
After deleting record we need to reset identity columns. we can do this by following command if we run same command after truncate table, Identity value start with 0 To check last value of identity using DBCC
dm_exec_query_stats, dm_exec_query_plan
List all SQL server table that do not have primary key
Let us check this query which use internal objects and get more information about installed SQL server using TSQL Below query show result in single row This query show result in two column
Forced manual fail-over with data loss (2 step) To force failover with data loss, connect to the SQL Server instance that hosts the target secondary replica and then run the following command: When the previous primary replica recovers, it will also assume the primary role. To ensure that the previous primary replica transitions into a …
Using TSQL we can find all dependent objects from all databases including remote server linked objects (sql_expression_dependencies is ) Contains one row for each by-name dependency on a user-defined entity in the current database. This includes dependencies between natively compiled, scalar user-defined functions, and other SQL Server modules. https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-expression-dependencies-transact-sql?view=sql-server-ver15
To retrieve the SQL Server Installation date, we have to check the login “NT Authority\System” creation date which gets created at the time of SQL Server installation.
