SQL Generic Script

1. When restoring data, culling takes:

ALTER DATABASE golf7_jhw SET OFFLINE with ROLLBACK IMMEDIATE

ALTER Database GOLF7_JHW Set Online

2. query table field Description sql:

SELECT Objname,value from:: Fn_listextendedproperty (NULL, ' user ', ' dbo ', ' table ', ' name ', ' column ', default)

4. Querying for duplicate data
For example: SELECT * FROM dbo. T_club WHERE Clubcode in (SELECT clubcode from dbo. T_club GROUP by Clubcode have COUNT (*) > 1)

5. Only one bar is displayed when duplicate data is queried
For example: SELECT * FROM dbo. T_club WHERE clubid in (SELECT MAX (clubid) from dbo. T_club GROUP by Clubcode)

3. Dynamic SQL Script (SP):

--=======================================================
--created By:yzh
--created date:2015-12-31
--description: View Member Benefits
--test:exec p_getequityplaynum ' F2015 ', 11
--=======================================================
ALTER proc [dbo]. [P_getequityplaynum]
(
@MembershipCardNo varchar (200)
, @ClubID int
)
As
Begin
declare @sqlStr nvarchar (max)
Set @sqlStr = '
Select
Clubid
, Regioninnerweekdaynumber
, Regioninnerholidaynumber
, Limitregioninnernumber
, Regionouterweekdaynumber
, Regionouterholidaynumber
, Limitregionouternumber
From T_equityplaynum E
Inner join t_membership m on e.membertype = M.membershiptypeid '
--Ball Club ID
If not (@ClubID is null or @ClubID =0)
Begin
Set @sqlStr = @sqlStr + case if charindex (' where ', @sqlStr) > 0 Then ' and ' Else ' WHERE ' end;
Set @[email protected] + ' E.clubid = ' + Convert (varchar, @ClubID);
End
--Membership card number
If not (@MembershipCardNo is null or @MembershipCardNo = ')
Begin
Set @sqlStr = @sqlStr + case if charindex (' where ', @sqlStr) > 0 Then ' and ' Else ' WHERE ' end;
Set @[email protected]+ ' M.membershipcardno = ' + @MembershipCardNo + ';
End
--print @sqlStr
EXEC sp_executesql @sqlStr
End

GO

SQL Generic Script