Useful SQL queries for every day use

Our application requirement changes from day to day and same we have to do our work on the database side. There are n numbers of SQL operations we do in a database like creating tables, updating table, add columns, add foreign key relation and many more as per our requirements.

As requirement comes I have to made some changes in database using SQL queries and sometimes I completely forgot about the sql query. So I am going to create a list of all useful sql queries for every day use. I hope this will also help you out.

Create table with unique identity with Foreign key

CREATE TABLE [dbo].[TableName] (
    Id int not null IDENTITY(1,1) PRIMARY KEY,
    ShouldRemove bit NOT NULL,
    CreatedBy int not null
    UpdatedOn datetime not null,
    UpdatedBy int not null CONSTRAINT FK_SystemSetting_User FOREIGN KEY (UpdatedBy) REFERENCES nw.[user] (Id) 
);

Alter table to add Foreign key

 ALTER TABLE dbo.TableName
 ADD CONSTRAINT FK_TableName_User FOREIGN KEY (CreatedBy)     
 REFERENCES nw.[user] (Id) 

Alter table to update column datatype

 ALTER TABLE [dbo].[TableName]
 ALTER COLUMN ColumnName BIT 

Bring foreign key relation details

 
SELECT
  object_name(parent_object_id) ParentTableName,
  object_name(referenced_object_id) RefTableName,
  name 
FROM sys.foreign_keys
WHERE parent_object_id = object_id('[dbo].[TableName]')

Get Information about all columns in a table

 
SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable 'Is Nullable',
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('YourTableName')

Get comma-separated columns

DECLARE @Columns TABLE ( ColumnName varchar(500) NOT NULL )

INSERT INTO @Columns ( ColumnName )

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N’Dynamic_Data_AFRater’
ORDER BY ORDINAL_POSITION

SELECT STRING_AGG( CAST(c.ColumnName as nvarchar(MAX)),’, ‘)FROM @Columns AS c

Stay tuned many more will come to add