Monday, September 9, 2024

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.