نمایش دیتل در کنار هدر در کنارهم در sql server

برای اینکه جزییات یا ردیف های جدول دیتیل به صورت ستون در جدول هدر نمایش داده شوند از کد زیر به عنوان نمونه میتوانید استفاده کنید 

CREATE PROCEDURE OrderExample 

AS

CREATE TABLE #OrdersTable(

OrderNo int,

OrderDate date,

CustomerID int,

TotalItems int,

TotalAmount decimal(10,2)

)

INSERT INTO #OrdersTable VALUES(101, '2016-8-11', 354, 3, 30)

INSERT INTO #OrdersTable VALUES(102, '2016-8-12', 221, 2, 20)

INSERT INTO #OrdersTable VALUES(103, '2016-8-13', 67, 1, 20)

INSERT INTO #OrdersTable VALUES(104, '2016-8-14', 8965, 3, 40)

CREATE table #OrderedItems (

OrderNo int,

OrderItem varchar(10),

OrderQty int

)

INSERT INTO #OrderedItems VALUES(101, 'T-Shirt', 2)

INSERT INTO #OrderedItems VALUES(101, 'Bedsheet', 1)

INSERT INTO #OrderedItems VALUES(102, 'Pants', 2)

INSERT INTO #OrderedItems VALUES(103, 'Quilt', 1)

INSERT INTO #OrderedItems VALUES(104, 'T-Shirt', 2)

INSERT INTO #OrderedItems VALUES(104, 'Pants', 1)

DECLARE @itemNames varchar(max) = 

(SELECT  STUFF(( SELECT DISTINCT ',' + QUOTENAME(OrderItem)

FROM #OrderedItems FOR XML PATH('')), 1, 1, '') AS ColList)

DECLARE @sql varchar(max) = 'SELECT OrderNo, OrderDate, CustomerID, 

TotalItems, TotalAmount,' + @itemNames + ' FROM

(SELECT o.OrderNo, o.OrderDate, o.CustomerID, o.TotalItems, o.TotalAmount,

i.OrderItem, i.OrderQty 

FROM #OrdersTable o

INNER JOIN #OrderedItems i ON i.OrderNo = o.OrderNo) SubQ

PIVOT(SUM(OrderQty) FOR OrderItem IN (' + @itemNames + ')) as pvt'

exec (@sql)

DROP TABLE #OrdersTable

DROP TABLE #OrderedItems

پیدا کردن ایندکس های بدون استفاده یا Unused Index

ایندکسی که مورد استفاده قرار نمی گیرد دارای هزینه نگهداری اند.

با اجرای دستور زیر ایندکس های اضافه در دیتابیس خودتون میتونید پیدا کنید و دستور حذف هم در اخر هر کدام برای راحتی کار شما قرار داده شده است .

توجه: اگر برای آزمایش کوئری پایین ، یک ایندکس را در همین لحظه تعریف کردید و سپس کوئری را روی پایگاه داده بلافاصله اجرا نمودید، به شما جواب نخواهد داد، برای بدست آوردن نتیجه ی مناسب، نیاز به زمان و ورود و خروج اطلاعات دارید.

SELECT TOP 100

o.name AS ObjectName

, i.name AS IndexName

, i.index_id AS IndexID

, dm_ius.user_seeks AS UserSeek

, dm_ius.user_scans AS UserScans

, dm_ius.user_lookups AS UserLookups

, dm_ius.user_updates AS UserUpdates

, p.TableRows

, 'DROP INDEX ' + QUOTENAME(i.name)

+ ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'

FROM sys.dm_db_index_usage_stats dm_ius

INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID

INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID

INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID

FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p

ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID

WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1

AND dm_ius.database_id = DB_ID()

AND i.type_desc = 'nonclustered'

AND i.is_primary_key = 0

AND i.is_unique_constraint = 0

ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

GO

نمایش تعداد رکوردهای کل جداول در دیتابیس sql

برای نمایش تعداد رکوردهای کل جداول در دیتابیس sql  میتوانید از دستور زیر استفاده نمایید 

SELECT SCHEMA_NAME(t.[schema_id]) AS [table_schema]
          ,OBJECT_NAME(p.[object_id]) AS [table_name]
          ,SUM(p.[rows]) AS [row_count]
    FROM [sys].[partitions] p
    INNER JOIN [sys].[tables] t ON p.[object_id] = t.[object_id]
    WHERE p.[index_id] < 2
    GROUP BY p.[object_id]
        ,t.[schema_id]
    ORDER BY 1, 2 ASC

تاریخ شروع و پایان هفته در sql server

برای به دست اوردن هفته میلادی از توابع زیر میتونید استفاده کنید و برای پیدا کردن هفته شمسی یک روز از این تاریخ کم کنید

SELECT DATEADD(wk, -1, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --first day previous week

SELECT DATEADD(wk, 0, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --first day current week

SELECT DATEADD(wk, 1, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --first day next week

SELECT DATEADD(wk, 0, DATEADD(DAY, 0-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --last day previous week

SELECT DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --last day current week

SELECT DATEADD(wk, 2, DATEADD(DAY, 0-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --last day next week

نمایش اعداد جامانده در یک ستون از جدول دیتابیس sql

برای نمایش اعدادی که در یک بازه خاص وجود ندارند یا به عبارتی فضای خالی بین اعداد را نشان دهد از کد زیر استفاده نمایید

;With CTERange

    As (

    select (select isnull(max(ArchiveID)+1,1) from tblArchives where ArchiveID < md.ArchiveID) as [from],

         md.ArchiveID - 1 as [to]

      from tblArchives md

      where md.ArchiveID != 1 and not exists (

            select 1 from tblArchives md2 where md2.ArchiveID = md.ArchiveID - 1)

    ) SELECT [from], [to], ([to]-[from])+1 [total missing]

    From CTERange 

    ORDER BY ([to]-[from])+1 DESC;

ایجاد عدد رندم در sql

ایجاد عدد رندم  12 رقم ی در sql  از کد زیر استفاده نمایید

convert(numeric(12,0),rand() * 999999999999) 

جستجو یک نام در پراسیجرها و توابع دیتابیس sql

جستجو یک نام در  پراسیجرها و توابع دیتابیس  sql

SELECT [Scehma]=schema_name(o.schema_id), o.Name, o.type 

FROM sys.sql_modules m

INNER JOIN sys.objects o

ON o.object_id = m.object_id

WHERE m.definition like '%name%'

GO

جستجو در mysql

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE "%search_string%";

پرش فیلد identity به 1000 بعد از هر restart دیتابیس

SQL Server identity values jump by 1000 when it is restarted

برای این که این اتفاق نیافتد کافی است مراحل زیر را طی کنید :

• Open "SQL Server Configuration Manager"
• Click "SQL Server Services" on the left pane
• Right-click on your SQL Server instance name on the right pane ->Default: SQL Server(MSSQLSERVER)
• Click "Properties"
• Click "Startup Parameters"
• On the "specify a startup parameter" textbox type "-T272"
• Click "Add"

Select top 10 records for each category

انتخاب ده رکورد اول از هر ایتم خاص در sql

SELECT rs.Field1,rs.Field2 
FROM (
    SELECT Field1,Field2, ROW_NUMBER() 
      OVER (Partition BY Section
            ORDER BY RankCriteria DESC ) AS Rank
    FROM table
    ) rs WHERE Rank <= 10

تولید یک کد ترکیبی در sql

تولید یک کد ترکیبی  از کاراکترهای کوچک و بزرگ و اعداد در اس کیو ال سرور  که می تواند به عنوان مثال برای کد هدیه  استفاده شود

DECLARE @new_id VARCHAR(200)

SELECT @new_id = NEWID()
SELECT CAST((ABS(CHECKSUM(@new_id))%10) AS VARCHAR(1)) + 
CHAR(ASCII('a')+(ABS(CHECKSUM(@new_id))%25)) +
CHAR(ASCII('A')+(ABS(CHECKSUM(@new_id))%25)) +
LEFT(@new_id,3)

جایگزینی حرف 'ی' و 'ک' عربی با فارسی

کد زیر تمامی جداول دیتابیس را میگردد و فیلدهای متنی را انتخاب میکند و این دو کاراکتر را جایگزین میکند 

DECLARE @Table NVARCHAR(800),@Column NVARCHAR(800)

DECLARE Table_Cursor CURSOR

FOR

--پیدا کردن تمام فیلدهای متنی تمام جداول دیتابیس جاری

SELECT T.name, /* Table */

       C.name  /* Column */

FROM sysobjects T,syscolumns C

WHERE  T.id = C.id  AND T.xtype = 'u' /* User Table */

          AND (C.xtype = 99 /* ntext */

OR C.xtype = 35   /* text */

OR C.xtype = 231  /* nvarchar */

OR C.xtype = 167  /* varchar */

OR C.xtype = 175  /* char */

OR C.xtype = 239  /* nchar */)

OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @Table,@Column

WHILE (@@FETCH_STATUS = 0)

BEGIN

EXEC ('Update [' + @Table + '] Set [' + @Column + '] = REPLACE(REPLACE(CAST([' + @Column + '] as nvarchar(800)), NCHAR(1610), NCHAR(1740)), NCHAR(1603), NCHAR(1705))')

FETCH NEXT FROM Table_Cursor INTO @Table,@Column

END CLOSE Table_Cursor 

DEALLOCATE Table_Cursor

یا به صورت جدول به جدول 

update `tbl_name` set

`text` = replace(`text`, 'ک', 'ک') ,

`text` = replace(`text`, 'ی', 'ی');

چیدن تاریخ در یک بازه زمانی در sql

من نیاز داشتم به اینکه یک ماه تاریخ در بالای گریدم نمایش بدم و سمت راستم هم یک سری اطلاعات دیگه . کد زیر تاریخ از اول ماه تا تاریخ جاری نمایش میدهد شما میتونید هر طور خواستین تغییرش بدین . 

DECLARE @startDate DATEtime

set @startDate= CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '01/' +  + CAST(YEAR(GETDATE()) AS VARCHAR)  -- mm/dd/yyyy

DECLARE @endDate DATEtime

set @endDate= GETDATE() -- mm/dd/yyyy

SELECT [Date] = DATEADD(Day,Number,@startDate) 

FROM  master..spt_values 

WHERE Type='P'

AND DATEADD(day,Number,@startDate) <= @endDate

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

چیدن تاریخ به ماه 

DECLARE @startDate VARCHAR(10)

DECLARE @endDate VARCHAR(10)

set @startDate= '2021/01/20'  -- mm/dd/yyyy

set @endDate= '2021/04/05'-- mm/dd/yyyy

SELECT DATEADD(MONTH, x.number, @StartDate)

FROM    master.dbo.spt_values x

WHERE   x.type = 'P'        

AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);

جدا کردن رشته با کاما در sql

ورودی یک رشته و کاراکتر جداکننده است و به صورت تفکیک شده در یک جدول به ما برگشت داده میشود . 

CREATE FUNCTION SplitString

(    

      @Input NVARCHAR(MAX),

      @Character CHAR(1)

)

RETURNS @Output TABLE (

      Item NVARCHAR(1000)

)

AS

BEGIN

      DECLARE @StartIndex INT, @EndIndex INT

 

      SET @StartIndex = 1

      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character

      BEGIN

            SET @Input = @Input + @Character

      END

 

      WHILE CHARINDEX(@Character, @Input) > 0

      BEGIN

            SET @EndIndex = CHARINDEX(@Character, @Input)

           

            INSERT INTO @Output(Item)

            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

           

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))

      END

 

      RETURN

END

GO

فراخوانی به صورت زیر

SELECT Item

FROM dbo.SplitString('Apple,Mango,Banana,Guava', ',')

نمایش چندین رکورد جدول sql server در یک عبارت

به عنوان مثال

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

که در این مثال نمایش میخواهیم به صورت زیر باشد :

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

از کدهای زیر می توان استفاده کرد

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name 
FROM People

یا

declare @phone varchar(max)='' 
   select @phone=@phone + mobileno +',' from  members
   select @phone

یا

DECLARE @Txt1 VARCHAR(MAX)

SET @Txt1=''

 

SELECT  @Txt1 = @Txt1 + Txt +','

FROM    ConcatenationDemo

SELECT  LEFT(@Txt1,LEN(@Txt1)-1) AS Txt

دستور Exec sp_Execute با پارامتر خروجی در sql server

Declare @TableName Varchar(100)

Set @TableName = 'TableName'
Declare @Count int
Declare @SqlString Nvarchar(1000)

Set @SqlString = 'Select @OutCount = Count(*) From ' +@TableName 
 
Exec sp_Executesql @SqlString, N'@OutCount Int Output', @OutCount = @Count Output