Remove Spaces In Every Column of Given Table

Comments :- 

Exec Ltrim_Rtirm 'Table_name'

I used simple statement in this proc
  UPDATE TABLE NAME
  SET COL1_NAME=LTRIM(RTRIM(COL1_NAME))
  ,COL2_NAME=LTRIM(RTRIM(COL2_NAME))

You can't update an identity column , so i removed identity column in this statement.

Proc :- 

create proc ltrim_rtrim
(
@table_name varchar(200)
)
as
begin
if not exists (select top 1 1 from information_schema.tables where table_name=@table_name)
begin
raiserror('......table not exists.......',16,2)
return
end

declare @qry as varchar(max)
set @qry=(select 'update ' + @table_name + char(10) + ' set ' +
isnull(stuff((select  ',[' + cast(column_name as varchar) + ']='
+ 'ltrim(rtrim(['+ cast(column_name as varchar) + ']))' + char(10)
from information_schema.columns
where table_name=@table_name
and ( column_name in (select column_name
from information_schema.columns
where table_name=@table_name
and column_name not in(select column_name
from information_schema.columns
where table_schema = 'dbo'
and table_name=@table_name
and columnproperty(object_id(table_name), column_name, 'isidentity') = 1)
))
for xml path('')),1,1,''),'''''') )
--print(@qry)
exec(@qry)
end

Clubbing of one column based on referring(grouping) of other column

Comments :- 

In below example,I have a #temp table with 2 columns named A,B

Here I am applying group by clause on 'B' column and getting 'A' column's
CSV(comma separated values) correspond to grouping.

Example :- 

create table #temp
(
a int,
b varchar(10)
)

insert into #temp
select 1,'A'
union all
select 2,'A'
union all
select 1,'B'
union all
select 2,'B'


select * from #temp

;with temptable as
(
select b from #temp group by b
)

select  stuff((select ','+ cast(#temp.a as varchar)
  from #temp
  where #temp.b=temptable.b
  for xml path('')),1,1,'')
,temptable.b
from temptable

drop table #temp

Main table structure :- 

Final Result :- 

Get new created table names in new databse by comparing 2 databases

select table_name 
from NEW_DATABASE_NAME.information_schema.columns as new
where not exists (select 'x' from OLD_DATABASE_NAME.information_schema.columns as old 
 where old.table_name=new.table_name
 and old.column_name=new.column_name)
and table_name not in (select distinct table_name 
  from OLD_DATABASE_NAME.information_schema.columns as old)
group by table_name

Generate default constraint scripts of selected database

SET NOCOUNT ON

SELECT 'ALTER TABLE ' + B.NAME + char(10) 
+'ADD CONSTRAINT ' + C.NAME + ' DEFAULT ' + C.DEFINITION + ' FOR ' + A.NAME + CHAR(10) 
FROM SYS.ALL_COLUMNS A 
INNER JOIN
SYS.TABLES B 
ON 
A.OBJECT_ID = B.OBJECT_ID
INNER JOIN
SYS.DEFAULT_CONSTRAINTS C
ON A.DEFAULT_OBJECT_ID = C.OBJECT_ID


/*====================================

alter table TABLE_NAME
ADD CONSTRAINT CONSTRAINT_NAME
default (DEFAULT_VALUE) for COLUMN_NAME


================================= */

SET NOCOUNT OFF

Generate Foreign Key scripts of selected database

SET NOCOUNT ON

      ;WITH CTE
      AS(
SELECT  RC.CONSTRAINT_NAME FK_NAME
, KF.TABLE_NAME FK_TABLE
, KF.COLUMN_NAME FK_COLUMN
, RC.UNIQUE_CONSTRAINT_NAME PK_NAME
, KP.TABLE_NAME PK_TABLE
, KP.COLUMN_NAME PK_COLUMN
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME
)
SELECT DISTINCT 'IF NOT EXISTS(SELECT TOP 1 1 FROM  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ' + CHAR(10) + 
'WHERE CONSTRAINT_NAME=''' + FK_NAME +''')' + CHAR(10) +
'BEGIN' + CHAR(10) + CHAR(9) +
'ALTER TABLE ' + FK_TABLE +  CHAR(10) + CHAR(9) +
'ADD CONSTRAINT '+ FK_NAME + ' FOREIGN KEY (' + 
       STUFF( (SELECT   ','+FK_COLUMN
               FROM CTE P2
               WHERE P2.FK_NAME = P1.FK_NAME
  AND   P2.FK_TABLE = P1.FK_TABLE
  GROUP BY FK_COLUMN
  ORDER BY FK_COLUMN
               FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'') 
  + ')' +  CHAR(10) + CHAR(9) + 
'REFERENCES ' + PK_TABLE +'('+ 
STUFF( (SELECT   ','+PK_COLUMN
               FROM CTE P3
               WHERE P3.PK_TABLE = P1.PK_TABLE
  AND   P3.PK_NAME = P1.PK_NAME
  GROUP BY PK_COLUMN
  ORDER BY PK_COLUMN
  FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') ,1,1,'')   + ')' +  CHAR(10) +
'END' + CHAR(10) + 
'ELSE' + CHAR(10) + 
'BEGIN' + CHAR(10) + CHAR(9) +
'SELECT ''' + FK_NAME + ' ALREADY EXISTS.''' + CHAR(10) + 
'END' + CHAR(10) + CHAR(10)
FROM CTE P1;

SET NOCOUNT OFF

Result :-

Generate primary Key scripts of selected database

SET NOCOUNT ON

;WITH CTE
AS(
SELECT   TABLE_NAME,CONSTRAINT_NAME AS RO,  COLUMN_NAME AS VAL
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'ISPRIMARYKEY') = 1

)
SELECT DISTINCT 'IF NOT EXISTS(SELECT TOP 1 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE' + CHAR(10) +
   'WHERE CONSTRAINT_NAME=''' + RO + '''' + CHAR(10) +
'AND TABLE_NAME = ''' + TABLE_NAME + ''')' + CHAR(10) +
   'BEGIN' + CHAR(10) + CHAR(9) +
'ALTER TABLE '+ TABLE_NAME + CHAR(10) + CHAR(9) +
'ADD CONSTRAINT ' + RO + ' PRIMARY KEY ('+D.VAL+')' + CHAR(10) +
'END' + CHAR(10) +
'ELSE' + CHAR(10) +
'BEGIN' + CHAR(10) + CHAR(9) +
'SELECT ''' + RO + ' ALREADY EXISTS.''' + CHAR(10) +
'END' + CHAR(10)
FROM CTE P1
CROSS APPLY ( SELECT
 STUFF( (SELECT ','+VAL
  FROM CTE P2
  WHERE P2.RO = P1.RO
  ORDER BY RO
  FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)')
,1,1,'')
)  D ( VAL )
   

SET NOCOUNT OFF

Result :-

How to recover the deleted records from SQL SERVER

Please follow the link given below.

How to recover the deleted records from SQL SERVER