How to accept multiple nulls for unique key column in sql server

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

LTRIM RTRIM is not working

ALTER FUNCTION FFCSSuite.[dbo].[udfTrim]
(
    @StringToClean as varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN   
    --Replace all non printing whitespace characers with Characer 32 whitespace
    --NULL
    Set @StringToClean = Replace(@StringToClean,CHAR(0),CHAR(32));
    --Horizontal Tab
    Set @StringToClean = Replace(@StringToClean,CHAR(9),CHAR(32));
    --Line Feed
    Set @StringToClean = Replace(@StringToClean,CHAR(10),CHAR(32));
    --Vertical Tab
    Set @StringToClean = Replace(@StringToClean,CHAR(11),CHAR(32));
    --Form Feed
    Set @StringToClean = Replace(@StringToClean,CHAR(12),CHAR(32));
    --Carriage Return
    Set @StringToClean = Replace(@StringToClean,CHAR(13),CHAR(32));
    --Column Break
    Set @StringToClean = Replace(@StringToClean,CHAR(14),CHAR(32));
    --Non-breaking space
    Set @StringToClean = Replace(@StringToClean,CHAR(160),CHAR(32));

    Set @StringToClean = LTRIM(RTRIM(@StringToClean));
    Return @StringToClean
END
GO

Query to List the tables with out Identity Columns

SELECT TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME NOT IN (
SELECT
  [table] = t.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
WHERE  EXISTS
(
  SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
)
AND s.name = 'dbo'
)

Query to list the tables which doesn't have Primary key

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE  TABLE_SCHEMA = 'dbo'
AND TABLE_NAME NOT IN
(
    SELECT 
    Tab.TABLE_NAME
    from
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY'
    AND Tab.table_schema = 'dbo'
)

Query to get All table columns with its datatype

SELECT SCHEMA_NAME(t.schema_id) AS schema_name,
t.name AS table_name,
c.name AS column_name,
ty.name AS datatype
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE ty.name = 'varchar'
ORDER BY schema_name, table_name;

List identity columns in sql server

SELECT
  [schema] = s.name,
  [table] = t.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
WHERE  EXISTS
(
  SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
)
AND s.name = 'dbo'

Change the schema of a table in sql server

ALTER SCHEMA [SCHEMA-NAME] TRANSFER [TABLE-NAME]

Eg: Alter Schema Emp Transfer dbo.Employee