How do I create a unique constraint on a column in SQL Server?

 You can create a unique constraint using the ALTER TABLE command:

ALTER TABLE YourTableName ADD CONSTRAINT Unique_Constraint_Name UNIQUE (ColumnName);

This enforces a unique constraint on ColumnName.

This returns the names of all databases on the server.

 To remove a column from a table, use the ALTER TABLE command:

ALTER TABLE YourTableName DROP COLUMN ColumnName;

This will permanently delete the column from the table.

How do I list all available databases on a SQL Server instance?

 You can list all databases on a SQL Server instance using:

SELECT name FROM sys.databases;

This returns the names of all databases on the server.

How do I check the SQL Server version and edition?

 Use the following query to get the SQL Server version and edition:

SELECT SERVERPROPERTY('ProductVersion') AS Version, 

 SERVERPROPERTY('ProductLevel') AS ProductLevel,

 SERVERPROPERTY('Edition') AS Edition;

This returns the version, product level (e.g., RTM, SP1), and edition of the SQL Server instance.

How do I rename a column in SQL Server?

 You can rename a column using the sp_rename system stored procedure:

EXEC sp_rename 'YourTableName.OldColumnName', 'NewColumnName', 'COLUMN';

This renames OldColumnName to NewColumnName in the specified table.

How do I grant a user access to a specific database in SQL Server?

 To grant access to a user for a specific database, use the following commands:

Create a login (if it doesn't already exist):

CREATE LOGIN YourLoginName WITH PASSWORD = 'YourPassword';

Grant access to the database:

USE YourDatabaseName; CREATE USER YourLoginName FOR LOGIN YourLoginName;

Grant a role or specific permissions:

EXEC sp_addrolemember 'db_datareader', 'YourLoginName';  -- Grants read access

How do I execute a stored procedure in SQL Server?

 To execute a stored procedure, use the EXEC command followed by the procedure name and any necessary parameters:

EXEC YourStoredProcedureName @Param1 = 'Value1', @Param2 = 'Value2';

Replace YourStoredProcedureName with the name of the stored procedure and provide the required parameter values.

How do I find all stored procedures in a database?

 You can retrieve the list of all stored procedures using the following query:

SELECT 
    name AS ProcedureName,
    create_date,
    modify_date
FROM 
    sys.procedures
ORDER BY 
    name;
This returns all stored procedures along with their creation and modification dates.

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.

How can I backup a database in SQL Server?

 You can backup a database using the following command:

BACKUP DATABASE YourDatabaseName TO DISK = 'C:\Backup\YourDatabaseName.bak';

This command creates a backup of the YourDatabaseName database to the specified location.

How do I enable a query execution plan in SQL Server?

  To enable the execution plan for a query, run:

SET SHOWPLAN_ALL ON;

Then run your query. The execution plan will be displayed as part of the result. To turn off the execution plan, use:

SET SHOWPLAN_ALL OFF;

How do I find the largest tables in a SQL Server database?

 You can use the following query to find the largest tables by data size:

SELECT t.NAME AS TableName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE t.NAME NOT LIKE 'dt%' AND i.OBJECT_ID > 255 GROUP BY t.Name, p.Rows ORDER BY TotalSpaceKB DESC;

This query returns the largest tables by row count and space usage.

How can I retrieve a list of all users in a specific SQL Server database?

 You can get the list of all users in a database using this query:

SELECT name AS Username FROM sys.database_principals WHERE type IN ('S', 'U') -- 'S' for SQL user, 'U' for Windows user AND name NOT LIKE '##%'; -- Exclude system users

This lists all users in the current database.

How can I find out the currently running queries in SQL Server?

 Use the following query to find currently running queries:

SELECT r.session_id, r.start_time, s.text AS query_text, r.status, r.command, r.total_elapsed_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s;

This returns details about all currently running queries, including the session ID, query text, and execution status.

How do I check the status of a SQL Server transaction?

 You can check the status of running transactions using this query:

SELECT session_id, transaction_id, database_id, transaction_begin_time, transaction_state, transaction_status

FROM sys.dm_tran_active_transactions;

This query gives you information about active transactions in SQL Server.

How do I list all tables in a specific database?

 You can use the following query to list all tables in the current database:

SELECT TABLE_NAME 

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'YourDatabaseName';

This query returns the names of all base tables in the specified database.

How can I check the size of a specific database in SQL Server?

 You can check the size of a database using the following query:

EXEC sp_spaceused;

Or, for a specific database:

USE YourDatabaseName;

EXEC sp_spaceused;

This command returns the database size, reserved space, unallocated space, and more.

How do I find the SPID (Session Process ID) for a specific database in SQL Server?

 SELECT 

    es.session_id AS SPID, 

    es.login_name, 

    es.status, 

    es.host_name, 

    es.program_name, 

    es.database_id, 

    DB_NAME(es.database_id) AS database_name,

    ec.client_net_address,

    er.wait_type, 

    er.wait_time, 

    er.blocking_session_id

FROM sys.dm_exec_sessions es

LEFT JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id

LEFT JOIN sys.dm_exec_requests er ON es.session_id = er.session_id

WHERE es.database_id = DB_ID('YourDatabaseName');

------------------------------------------------------------------------------------

kill SSID