How can I list all foreign key constraints in a database?

To get a list of all foreign keys in the current database, use the following query:

SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, OBJECT_NAME(f.referenced_object_id) AS ReferencedTable, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferencedColumn FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id;

This query returns all foreign key constraints, including the related tables and columns.

How do I update data in a table based on conditions from another table?

 You can update data in one table using values from another table by using a join in the UPDATE statement:

UPDATE t1

SET t1.ColumnToUpdate = t2.NewValue

FROM YourTable1 t1

INNER JOIN YourTable2 t2

    ON t1.ID = t2.ID

WHERE t2.ConditionColumn = 'SomeValue';

This updates ColumnToUpdate in YourTable1 with values from NewValue in YourTable2 where a condition is met.

How do I add a column to an existing table in SQL Server?

 To add a new column to an existing table, use the ALTER TABLE command:

ALTER TABLE YourTableName ADD NewColumnName INT;

This adds a new column named NewColumnName with an INT data type to YourTableName.

How can I delete duplicate rows from a table?

 To delete duplicate rows while keeping one instance of each, you can use a Common Table Expression (CTE):

WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Column1, Column2 ORDER BY (SELECT NULL)) AS rn FROM YourTableName ) DELETE FROM CTE WHERE rn > 1;

This CTE assigns a row number to each duplicate row and deletes all but the first instance.

How do I find duplicate rows in a table?

 To find duplicate rows based on certain columns, you can use a query with the GROUP BY and HAVING clauses:

SELECT Column1, Column2, COUNT(*) FROM YourTableName GROUP BY Column1, Column2 HAVING COUNT(*) > 1;

Replace Column1, Column2 with the columns you want to check for duplicates.

How do I get the list of columns in a table with their data types in SQL Server?

 Use the following query to retrieve a list of all columns and their data types:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YourTableName';

This will return all column names, data types, and their maximum length for the specified table.

How do I rebuild or reorganize indexes in SQL Server?

 To rebuild all indexes on a table:

ALTER INDEX ALL ON YourTableName REBUILD;

To reorganize all indexes on a table:

ALTER INDEX ALL ON YourTableName REORGANIZE;

Rebuilding indexes reorganizes the entire index tree, while reorganizing is a lighter operation that defragments the leaf level of the index.

How do I view all indexes on a table in SQL Server?

 You can use the following query to list all indexes on a specific table:

SELECT i.name AS IndexName, i.type_desc AS IndexType, c.name AS ColumnName FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE i.object_id = OBJECT_ID('YourTableName');

Replace 'YourTableName' with the actual table name. This query returns all indexes and their corresponding columns for the specified table.

How do I get the last executed queries in SQL Server?

 You can retrieve the last executed queries using the following query:

SELECT deqs.creation_time, dest.text AS QueryText, deqs.execution_count, deqs.total_worker_time, deqs.total_physical_reads, deqs.total_logical_reads FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.creation_time DESC;

This gives you a list of recently executed queries along with execution statistics.

How do I find blocked sessions and the session that is blocking them?

 To identify blocked sessions and the session causing the block:

SELECT blocking_session_id AS BlockingSession, session_id AS BlockedSession, wait_type, wait_time, wait_resource FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;

This query shows all sessions that are currently blocked and the blocking session’s ID.