SQL JOIN TABLE VALUE TO STRING

SELECT STUFF((SELECT ',' + ISMI FROM MAGAZA_TANIM FOR XML PATH('')),1, 1, '')

ANKARA,ISTANBUL,IZMIR....

Split string with parsename- sql virtual array

DECLARE @test nvarchar(256)= 'lorem.er.deneme.test'

 

SELECT PARSENAME(@test,1),PARSENAME(@test,3),PARSENAME(@test,2),PARSENAME(@test,4)

 

DECLARE @FullName NVARCHAR(500)

 

SET @FullName = N'Flintstone, Mr.Fred, Jr'

 

SELECT PARSENAME(FName, 3) AS "Title",

       PARSENAME(FName, 2) AS "FirstName",

       PARSENAME(FName, 4) AS "LastName",

       PARSENAME(FName, 1) AS "Prefix"

  FROM ( SELECT REPLACE(@FullName, ', ', '.')) D(FName)

 

T-SQL DATENAME Turkish–Türkçe gün isimleri

SELECT

      DATENAME(dw, TimeStart) AS DayOfWeek,

      DATEPART(dw, TimeStart) AS DayNum

      FROM ExecutionLogs

      GROUP BY DATENAME(dw, TimeStart), DATEPART(dw, TimeStart)

DayOfWeek                      DayNum

------------------------------ -----------

Wednesday                      4

Tuesday                        3

Sunday                         1

Friday                         6

Thursday                       5

Saturday                       7

Monday                         2

 

(7 row(s) affected)

 

SET LANGUAGE Turkish;

           

SELECT

      DATENAME(dw, TimeStart) AS DayOfWeek,

      DATEPART(dw, TimeStart) AS DayNum

      FROM ExecutionLogs

      GROUP BY DATENAME(dw, TimeStart), DATEPART(dw, TimeStart)

Changed language setting to Türkçe.

DayOfWeek                      DayNum

------------------------------ -----------

Perşembe                       4

Pazartesi                      1

Pazar                          7

Salı                           2

Cumartesi                      6

Cuma                           5

Çarşamba                       3

 

(7 row(s) affected)

 

 

 

 

T-SQL system stored procedures 1

EXEC sp_spaceused

EXEC sp_spaceused dbo.table_name

database_name                                                                                                                    database_size      unallocated space

-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------

master                                                                                                                           5.25 MB            1.52 MB

 

reserved           data               index_size         unused

------------------ ------------------ ------------------ ------------------

2536 KB            1160 KB            1040 KB            336 KB

Shrink log file

 

USE [master]

GO

ALTER DATABASE [REPORT] SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE(ANGEL_log, 1)

ALTER DATABASE [REPORT] SET RECOVERY FULL WITH NO_WAIT

GO

Change Server Name –SQL Server Instance adını değiştirme

 

SELECT @@SERVERNAME AS 'Server Name'

 

sp_dropserver 'OLD_NAME'

GO

sp_addserver 'NEW_NAME', local

GO

Restart sql server instance

Adding Custom error message

Exec sp_addmessage

     @msgnum=50002,

     @severity=12,

     @msgtext='error while %s description ;',

     @with_log='False',

       @replace='replace'

       

 

select * from sys.messages

send mail to operator

EXECUTE

      msdb.dbo.sp_notify_operator

      @name=N'operator name',@subject=N'subject of mail',

      @body=N'content of mail message'

Requests of sql server **

            SELECT

            r.session_id,

            r.blocking_session_id,

            s.program_name,

            s.host_name, 

            t.text

      FROM

            sys.dm_exec_requests r

            INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id

            CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

      WHERE

            s.is_user_process = 1

 

 

report server subscription- agent list

    SELECT    

Schedule.ScheduleID as SubscriptionID,

Subscriptions.*,

Schedule.ScheduleID AS SQLAgent_Job_Name,

                  Subscriptions.Description AS sub_desc, Subscriptions.DeliveryExtension AS sub_delExt,

             [Catalog].Name AS ReportName, [Catalog].Path AS ReportPath

FROM         ReportServer.dbo.ReportSchedule

INNER JOIN ReportServer.dbo.Schedule ON ReportServer.dbo.ReportSchedule.ScheduleID = Schedule.ScheduleID

INNER JOIN ReportServer.dbo.Subscriptions ON ReportServer.dbo.ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID

INNER JOIN ReportServer.dbo.[Catalog] ON ReportServer.dbo.ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID

                WHERE          

         Subscriptions.DeliveryExtension = 'Report Server Email'

 

 

compare two tables dynamic- tablo sütunları karşılaştırma

DECLARE @dbname1 nvarchar(48) =N'ERP'

DECLARE @dbname2 nvarchar(48) =N'AERP'

 

DECLARE @TableName nvarchar(48) =N'FATURA'

 

 

SELECT * FROM (

      SELECT

      @dbname1 as DBNAME,

      clmns.name AS [Name],

      usrt.name AS [DataType],

      ISNULL(baset.name, N'') AS [SystemType],

      CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN

      clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],

      CAST(clmns.precision AS int) AS [NumericPrecision]

      FROM

      ERP.sys.tables AS tbl

      INNER JOIN ERP.sys.all_columns AS clmns ON clmns.object_id=tbl.object_id

      LEFT OUTER JOIN ERP.sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id

      LEFT OUTER JOIN ERP.sys.types AS baset ON baset.user_type_id = clmns.system_type_id and

      baset.user_type_id = baset.system_type_id

      WHERE

      (tbl.name=@TableName and SCHEMA_NAME(tbl.schema_id)=N'dbo') )as ERP

LEFT JOIN (

      SELECT

      @dbname2 as DBNAME,

      clmns.name AS [Name],

      usrt.name AS [DataType],

      ISNULL(baset.name, N'') AS [SystemType],

      CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN

      clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],

      CAST(clmns.precision AS int) AS [NumericPrecision]

      FROM

      AERP.sys.tables AS tbl

      INNER JOIN AERP.sys.all_columns AS clmns ON clmns.object_id=tbl.object_id

      LEFT OUTER JOIN AERP.sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id

      LEFT OUTER JOIN AERP.sys.types AS baset ON baset.user_type_id = clmns.system_type_id and

      baset.user_type_id = baset.system_type_id

      WHERE

      (tbl.name=@TableName and SCHEMA_NAME(tbl.schema_id)=N'dbo') ) AERP

      ON ERP.Name = AERP.Name

            WHERE

                  ERP.[Length] <> AERP.[Length]