<![CDATA[منتدى فيجوال بيسك لكل العرب | منتدى المبرمجين العرب - مكتبة أكواد Sql Server]]> https://vb4arb.com/vb/ Wed, 22 Apr 2026 21:55:54 +0000 MyBB <![CDATA[لمعرفة ان كانت السنة كبيسة أم لا]]> https://vb4arb.com/vb/showthread.php?tid=6432 Thu, 18 Oct 2012 03:27:12 +0000 Raggi Tech]]> https://vb4arb.com/vb/showthread.php?tid=6432 كاتب الموضوع : Boutemine Oualid

 السلام عليكم و رحمة الله و بركاته

كود :
CREATE FUNCTION IsLeapYear (@Annee AS int)
RETURNS bit
AS
BEGIN
Declare @Date29Fev AS Datetime
Declare @Result AS bit
SET @Date29Fev = DATEADD(DAY, -1, Cast('01/03/'+Cast(@Annee AS varchar(4)) AS datetime))
IF Day(@Date29Fev )=29
SET @Result =1
ELSE
SET @Result = 0
return @Result
END
]]> كاتب الموضوع : Boutemine Oualid

 السلام عليكم و رحمة الله و بركاته

كود :
CREATE FUNCTION IsLeapYear (@Annee AS int)
RETURNS bit
AS
BEGIN
Declare @Date29Fev AS Datetime
Declare @Result AS bit
SET @Date29Fev = DATEADD(DAY, -1, Cast('01/03/'+Cast(@Annee AS varchar(4)) AS datetime))
IF Day(@Date29Fev )=29
SET @Result =1
ELSE
SET @Result = 0
return @Result
END
]]> <![CDATA[لحساب الفرق بالأيام بين تارخين]]> https://vb4arb.com/vb/showthread.php?tid=6431 Thu, 18 Oct 2012 03:25:32 +0000 Raggi Tech]]> https://vb4arb.com/vb/showthread.php?tid=6431 كاتب الموضوع : Boutemine Oualid

 السلام عليكم و رحمة الله و بركاته

كود :
DECLARE @BirthDate AS DateTime;
SET @BirthDate = '1986-20-09'
SELECT DATEDIFF(day, @BirthDate, GetDate()) AS DayNumber;
]]> كاتب الموضوع : Boutemine Oualid

 السلام عليكم و رحمة الله و بركاته

كود :
DECLARE @BirthDate AS DateTime;
SET @BirthDate = '1986-20-09'
SELECT DATEDIFF(day, @BirthDate, GetDate()) AS DayNumber;
]]> <![CDATA[عكس سلسلة نصية]]> https://vb4arb.com/vb/showthread.php?tid=6430 Thu, 18 Oct 2012 03:21:00 +0000 Raggi Tech]]> https://vb4arb.com/vb/showthread.php?tid=6430 كاتب الموضوع : Boutemine Oualid

 السلام عليكم و رحمة الله و بركاته

كود :
[SIZE=2]DECLARE @Variable AS VARCHAR(50)[/SIZE]
[SIZE=2]SET @Variable = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'[/SIZE]
[SIZE=2]SELECT REVERSE(@Variable) [/SIZE]
[SIZE=2]AS INVERSION;[/SIZE]
]]> كاتب الموضوع : Boutemine Oualid

 السلام عليكم و رحمة الله و بركاته

كود :
[SIZE=2]DECLARE @Variable AS VARCHAR(50)[/SIZE]
[SIZE=2]SET @Variable = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'[/SIZE]
[SIZE=2]SELECT REVERSE(@Variable) [/SIZE]
[SIZE=2]AS INVERSION;[/SIZE]
]]> <![CDATA[ارسال رسائل الكترونية من ال SQL Server 2005]]> https://vb4arb.com/vb/showthread.php?tid=6429 Thu, 18 Oct 2012 03:19:47 +0000 Raggi Tech]]> https://vb4arb.com/vb/showthread.php?tid=6429 كاتب الموضوع : Boutemine Oualid

 السلام عليكم و رحمة الله و بركاته

كود :
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Work Order Report</H1>' +
N'<table border="1">' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th></tr>' +
CAST ( ( SELECT td = wo.WorkOrderID, '',
td = p.ProductID, '',
td = p.Name, '',
td = wo.OrderQty, '',
td = wo.DueDate, '',
td = (p.ListPrice - p.StandardCost) * wo.OrderQty
FROM AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2004-04-30'
AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
ORDER BY DueDate ASC,
(p.ListPrice - p.StandardCost) * wo.OrderQty DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name='Profil Article',
@recipients='danw@Adventure-Works.com',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;
]]> كاتب الموضوع : Boutemine Oualid

 السلام عليكم و رحمة الله و بركاته

كود :
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Work Order Report</H1>' +
N'<table border="1">' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th></tr>' +
CAST ( ( SELECT td = wo.WorkOrderID, '',
td = p.ProductID, '',
td = p.Name, '',
td = wo.OrderQty, '',
td = wo.DueDate, '',
td = (p.ListPrice - p.StandardCost) * wo.OrderQty
FROM AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2004-04-30'
AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
ORDER BY DueDate ASC,
(p.ListPrice - p.StandardCost) * wo.OrderQty DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name='Profil Article',
@recipients='danw@Adventure-Works.com',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;
]]> <![CDATA[مثال للبحث عن الصفوف المتكررة]]> https://vb4arb.com/vb/showthread.php?tid=6012 Wed, 17 Oct 2012 10:12:02 +0000 Raggi Tech]]> https://vb4arb.com/vb/showthread.php?tid=6012 كاتب الموضوع : AhmedEssawy
كود :
SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
]]> كاتب الموضوع : AhmedEssawy
كود :
SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
]]> <![CDATA[ليست اصغر من]]> https://vb4arb.com/vb/showthread.php?tid=6011 Wed, 17 Oct 2012 10:11:26 +0000 Raggi Tech]]> https://vb4arb.com/vb/showthread.php?tid=6011 كاتب الموضوع : AhmedEssawy
الاستعلام عن الموظفين بشرط ان يتجاوز ID رقم خمسه

كود :
[color=#990000]1[/color][color=black]>[/color][FONT=Courier New]
[color=#990000]2[/color]> select * from [color=black]employee[/color]
[color=#990000]3[/color][color=black]> GO[/color]
[color=black]ID name salary start_date city region[/color]
[color=black]----------- ---------- ----------- ----------------------- ---------- ------[/color]
[color=#990000]1 [/color][color=black]Jason [/color][color=#990000]40420 1994[/color][color=black]-[/color][color=#990000]02[/color][color=black]-[/color][color=#990000]01 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]New York W[/color]
[color=#990000]2 [/color][color=black]Robert [/color][color=#990000]14420 1995[/color][color=black]-[/color][color=#990000]01[/color][color=black]-[/color][color=#990000]02 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Vancouver N[/color]
[color=#990000]3 [/color][color=black]Celia [/color][color=#990000]24020 1996[/color][color=black]-[/color][color=#990000]12[/color][color=black]-[/color][color=#990000]03 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Toronto W[/color]
[color=#990000]4 [/color][color=black]Linda [/color][color=#990000]40620 1997[/color][color=black]-[/color][color=#990000]11[/color][color=black]-[/color][color=#990000]04 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]New York N[/color]
[color=#990000]5 [/color][color=black]David [/color][color=#990000]80026 1998[/color][color=black]-[/color][color=#990000]10[/color][color=black]-[/color][color=#990000]05 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Vancouver W[/color]
[color=#990000]6 [/color][color=black]James [/color][color=#990000]70060 1999[/color][color=black]-[/color][color=#990000]09[/color][color=black]-[/color][color=#990000]06 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Toronto N[/color]
[color=#990000]7 [/color][color=black]Alison [/color][color=#990000]90620 2000[/color][color=black]-[/color][color=#990000]08[/color][color=black]-[/color][color=#990000]07 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]New York W[/color]
[color=#990000]8 [/color][color=black]Chris [/color][color=#990000]26020 2001[/color][color=black]-[/color][color=#990000]07[/color][color=black]-[/color][color=#990000]08 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Vancouver N[/color]
[color=#990000]9 [/color][color=black]Mary [/color][color=#990000]60020 2002[/color][color=black]-[/color][color=#990000]06[/color][color=black]-[/color][color=#990000]09 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Toronto W[/color]

[color=black]([/color][color=#990000]9 [/color][color=black]rows affected)[/color]
[color=#990000]1[/color][color=black]>[/color]
[color=#990000]2[/color][color=black]> --Not less than.[/color]
[color=#990000]3[/color][color=black]>[/color]
[color=#990000]4[/color]> select * from employee where [color=black]id !< [/color][color=#990000]5[/color]
[color=#990000]5[/color][color=black]> GO[/color]
[color=black]ID name salary start_date city region[/color]
[color=black]----------- ---------- ----------- ----------------------- ---------- ------[/color]
[color=#990000]5 [/color][color=black]David [/color][color=#990000]80026 1998[/color][color=black]-[/color][color=#990000]10[/color][color=black]-[/color][color=#990000]05 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Vancouver W[/color]
[color=#990000]6 [/color][color=black]James [/color][color=#990000]70060 1999[/color][color=black]-[/color][color=#990000]09[/color][color=black]-[/color][color=#990000]06 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Toronto N[/color]
[color=#990000]7 [/color][color=black]Alison [/color][color=#990000]90620 2000[/color][color=black]-[/color][color=#990000]08[/color][color=black]-[/color][color=#990000]07 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]New York W[/color]
[color=#990000]8 [/color][color=black]Chris [/color][color=#990000]26020 2001[/color][color=black]-[/color][color=#990000]07[/color][color=black]-[/color][color=#990000]08 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Vancouver N[/color]
[color=#990000]9 [/color][color=black]Mary [/color][color=#990000]60020 2002[/color][color=black]-[/color][color=#990000]06[/color][color=black]-[/color][color=#990000]09 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Toronto W[/color]

[color=black]([/color][color=#990000]5 [/color][color=black]rows affected)[/color][/FONT]
]]> كاتب الموضوع : AhmedEssawy
الاستعلام عن الموظفين بشرط ان يتجاوز ID رقم خمسه

كود :
[color=#990000]1[/color][color=black]>[/color][FONT=Courier New]
[color=#990000]2[/color]> select * from [color=black]employee[/color]
[color=#990000]3[/color][color=black]> GO[/color]
[color=black]ID name salary start_date city region[/color]
[color=black]----------- ---------- ----------- ----------------------- ---------- ------[/color]
[color=#990000]1 [/color][color=black]Jason [/color][color=#990000]40420 1994[/color][color=black]-[/color][color=#990000]02[/color][color=black]-[/color][color=#990000]01 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]New York W[/color]
[color=#990000]2 [/color][color=black]Robert [/color][color=#990000]14420 1995[/color][color=black]-[/color][color=#990000]01[/color][color=black]-[/color][color=#990000]02 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Vancouver N[/color]
[color=#990000]3 [/color][color=black]Celia [/color][color=#990000]24020 1996[/color][color=black]-[/color][color=#990000]12[/color][color=black]-[/color][color=#990000]03 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Toronto W[/color]
[color=#990000]4 [/color][color=black]Linda [/color][color=#990000]40620 1997[/color][color=black]-[/color][color=#990000]11[/color][color=black]-[/color][color=#990000]04 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]New York N[/color]
[color=#990000]5 [/color][color=black]David [/color][color=#990000]80026 1998[/color][color=black]-[/color][color=#990000]10[/color][color=black]-[/color][color=#990000]05 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Vancouver W[/color]
[color=#990000]6 [/color][color=black]James [/color][color=#990000]70060 1999[/color][color=black]-[/color][color=#990000]09[/color][color=black]-[/color][color=#990000]06 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Toronto N[/color]
[color=#990000]7 [/color][color=black]Alison [/color][color=#990000]90620 2000[/color][color=black]-[/color][color=#990000]08[/color][color=black]-[/color][color=#990000]07 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]New York W[/color]
[color=#990000]8 [/color][color=black]Chris [/color][color=#990000]26020 2001[/color][color=black]-[/color][color=#990000]07[/color][color=black]-[/color][color=#990000]08 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Vancouver N[/color]
[color=#990000]9 [/color][color=black]Mary [/color][color=#990000]60020 2002[/color][color=black]-[/color][color=#990000]06[/color][color=black]-[/color][color=#990000]09 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Toronto W[/color]

[color=black]([/color][color=#990000]9 [/color][color=black]rows affected)[/color]
[color=#990000]1[/color][color=black]>[/color]
[color=#990000]2[/color][color=black]> --Not less than.[/color]
[color=#990000]3[/color][color=black]>[/color]
[color=#990000]4[/color]> select * from employee where [color=black]id !< [/color][color=#990000]5[/color]
[color=#990000]5[/color][color=black]> GO[/color]
[color=black]ID name salary start_date city region[/color]
[color=black]----------- ---------- ----------- ----------------------- ---------- ------[/color]
[color=#990000]5 [/color][color=black]David [/color][color=#990000]80026 1998[/color][color=black]-[/color][color=#990000]10[/color][color=black]-[/color][color=#990000]05 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Vancouver W[/color]
[color=#990000]6 [/color][color=black]James [/color][color=#990000]70060 1999[/color][color=black]-[/color][color=#990000]09[/color][color=black]-[/color][color=#990000]06 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Toronto N[/color]
[color=#990000]7 [/color][color=black]Alison [/color][color=#990000]90620 2000[/color][color=black]-[/color][color=#990000]08[/color][color=black]-[/color][color=#990000]07 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]New York W[/color]
[color=#990000]8 [/color][color=black]Chris [/color][color=#990000]26020 2001[/color][color=black]-[/color][color=#990000]07[/color][color=black]-[/color][color=#990000]08 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Vancouver N[/color]
[color=#990000]9 [/color][color=black]Mary [/color][color=#990000]60020 2002[/color][color=black]-[/color][color=#990000]06[/color][color=black]-[/color][color=#990000]09 00[/color][color=black]:[/color][color=#990000]00[/color][color=black]:[/color][color=#990000]00.000 [/color][color=black]Toronto W[/color]

[color=black]([/color][color=#990000]5 [/color][color=black]rows affected)[/color][/FONT]
]]> <![CDATA[لعمل نسخ احتياطي يومي إلى مكان معين]]> https://vb4arb.com/vb/showthread.php?tid=6010 Wed, 17 Oct 2012 10:11:01 +0000 Raggi Tech]]> https://vb4arb.com/vb/showthread.php?tid=6010 كاتب الموضوع : AhmedEssawy
كود :
-- Code
declare @strDrive char(3)
declare @strDir nvarchar(30)
declare @strDB nvarchar(20)
select @strDrive = 'e:\'
select @strDir = 'sqlBak\'
select @strDB = 'A7Dev'
Declare @strYear char(1)
declare @strMonth char(2)
declare @strDay char(2)
declare @strHour char(2)
declare @strMin char(2)
declare @strUnique char(100)
declare @strDesc nvarchar(30)
declare @myfile char(100)
declare @mydir char(100)
declare @mdcmd char(100)
select @strDesc = @strDB + ' Backup'
-- Build the Year portion of the dir.
select @strYear = Right(Str(DatePart(yy, getdate())), 1)
-- Build the Month portion of the dir.
select @strMonth = Right('0' + LTrim(Str(DatePart(mm, getdate()))), 2)
-- Build the Day portion of the dir.
select @strDay = Right('0' + LTrim(Str(DatePart(dd, getdate())) ), 2)
-- Build the Hour portion of the dir.
select @strHour = Right('0' + LTrim(Str(DatePart(hh, getdate())) ), 2)
-- Build the Minute portion of the dir.
select @strMin = Right('0' + LTrim(Str(DatePart(mi, getdate())) ), 2)
-- Add the prefix to the directory.
select @strUnique = @strYear + @strMonth + @strDay + @strHour + @strMin
-- Backup the LocMon database.
select @mydir = @strDrive + @strDir + @strDB + '\b' + RTrim(@strUnique)
select @mdcmd = 'md ' + @mydir
-- print @mdcmd
exec master..xp_cmdshell @mdcmd
select @myfile = RTrim(@mydir) + '\' + @strDB + '.sqb'
BACKUP DATABASE @strDB TO DISK = @myfile WITH INIT, NOUNLOAD , NAME = @strDesc, SKIP , STATS = 10, DESCRIPTION = @strDesc, NOFORMAT
-- Usage
-- Create a SQL Job to run often. Paste this script into
-- the Command area of the wizard where you define the
-- steps.
-- Notes
-- You will need to change the DB variable, and maybe the
-- driver letter and folder. Otherwise, it should work
-- for you.
-----------------------------------------------------------
-- Title: Backup/Restore DB with script -- Language: SQL
-- Description: Might be useful for TDD with small
-- databases. Probably useful for rollout scripts
-- Author: Bram Borak
-- Code
USE master
GO
PRINT '--------------------------------------------------------'
GO
EXEC sp_addumpdevice 'disk', 'whateverDB_backup', 'c:\whateverDB_backup_'+REPLACE(REPLACE(REPLACE(CONVERT(char(25),GETDATE(),20),':','_'),' ','_'),'-','_')+'.dat'
GO
BACKUP DATABASE whateverDB TO whateverDB_backup
GO
EXEC sp_dropdevice 'whateverDB_backup'
GO
PRINT 'Backed up whateverDB_backup'
GO
PRINT '--------------------------------------------------------'
GO
----------------------------------------------------------

PRINT '--------------------------------------------------------'
GO
EXEC sp_addumpdevice 'disk', 'whateverDB_backup', 'c:\whateverDB_backup.dat'
GO
RESTORE DATABASE whateverDB FROM whateverDB_backup
GO
EXEC sp_dropdevice 'whateverDB_backup'
GO
PRINT 'Restored whateverDB'
GO
PRINT '--------------------------------------------------------'
GO
]]> كاتب الموضوع : AhmedEssawy
كود :
-- Code
declare @strDrive char(3)
declare @strDir nvarchar(30)
declare @strDB nvarchar(20)
select @strDrive = 'e:\'
select @strDir = 'sqlBak\'
select @strDB = 'A7Dev'
Declare @strYear char(1)
declare @strMonth char(2)
declare @strDay char(2)
declare @strHour char(2)
declare @strMin char(2)
declare @strUnique char(100)
declare @strDesc nvarchar(30)
declare @myfile char(100)
declare @mydir char(100)
declare @mdcmd char(100)
select @strDesc = @strDB + ' Backup'
-- Build the Year portion of the dir.
select @strYear = Right(Str(DatePart(yy, getdate())), 1)
-- Build the Month portion of the dir.
select @strMonth = Right('0' + LTrim(Str(DatePart(mm, getdate()))), 2)
-- Build the Day portion of the dir.
select @strDay = Right('0' + LTrim(Str(DatePart(dd, getdate())) ), 2)
-- Build the Hour portion of the dir.
select @strHour = Right('0' + LTrim(Str(DatePart(hh, getdate())) ), 2)
-- Build the Minute portion of the dir.
select @strMin = Right('0' + LTrim(Str(DatePart(mi, getdate())) ), 2)
-- Add the prefix to the directory.
select @strUnique = @strYear + @strMonth + @strDay + @strHour + @strMin
-- Backup the LocMon database.
select @mydir = @strDrive + @strDir + @strDB + '\b' + RTrim(@strUnique)
select @mdcmd = 'md ' + @mydir
-- print @mdcmd
exec master..xp_cmdshell @mdcmd
select @myfile = RTrim(@mydir) + '\' + @strDB + '.sqb'
BACKUP DATABASE @strDB TO DISK = @myfile WITH INIT, NOUNLOAD , NAME = @strDesc, SKIP , STATS = 10, DESCRIPTION = @strDesc, NOFORMAT
-- Usage
-- Create a SQL Job to run often. Paste this script into
-- the Command area of the wizard where you define the
-- steps.
-- Notes
-- You will need to change the DB variable, and maybe the
-- driver letter and folder. Otherwise, it should work
-- for you.
-----------------------------------------------------------
-- Title: Backup/Restore DB with script -- Language: SQL
-- Description: Might be useful for TDD with small
-- databases. Probably useful for rollout scripts
-- Author: Bram Borak
-- Code
USE master
GO
PRINT '--------------------------------------------------------'
GO
EXEC sp_addumpdevice 'disk', 'whateverDB_backup', 'c:\whateverDB_backup_'+REPLACE(REPLACE(REPLACE(CONVERT(char(25),GETDATE(),20),':','_'),' ','_'),'-','_')+'.dat'
GO
BACKUP DATABASE whateverDB TO whateverDB_backup
GO
EXEC sp_dropdevice 'whateverDB_backup'
GO
PRINT 'Backed up whateverDB_backup'
GO
PRINT '--------------------------------------------------------'
GO
----------------------------------------------------------

PRINT '--------------------------------------------------------'
GO
EXEC sp_addumpdevice 'disk', 'whateverDB_backup', 'c:\whateverDB_backup.dat'
GO
RESTORE DATABASE whateverDB FROM whateverDB_backup
GO
EXEC sp_dropdevice 'whateverDB_backup'
GO
PRINT 'Restored whateverDB'
GO
PRINT '--------------------------------------------------------'
GO
]]> <![CDATA[لعمل password عشوائية]]> https://vb4arb.com/vb/showthread.php?tid=6009 Wed, 17 Oct 2012 10:10:34 +0000 Raggi Tech]]> https://vb4arb.com/vb/showthread.php?tid=6009 كاتب الموضوع : AhmedEssawy

 الشرح ضمن الكود :


كود :
CREATE PROC random_password
(
@len int = 8, --Length of the password to be generated
@password_type char(7) = 'simple'
--Default is to generate a simple password with lowecase letters.
--Pass anything other than 'simple' to generate a complex password.
--The complex password includes numbers, special characters, upper case and lower case letters
)
AS
/***********************************************
Examples:
To generate a simple password with a length of 8 characters:
EXEC random_password
To generate a simple password with 6 characters:
EXEC random_password 6
To generate a complex password with 8 characters:
EXEC random_password @Password_type = 'complex'
To generate a comples password with 6 characters:
EXEC random_password 6, 'complex'
***********************************************/
BEGIN
DECLARE @password varchar(25), @type tinyint, @bitmap char(6)
SET @password=''
SET @bitmap = 'uaeioy'
--@bitmap contains all the vowels, which are a, e, i, o, u and y. These vowels are used to generate slightly readable/rememberable simple passwords
WHILE @len > 0
BEGIN
IF @password_type = 'simple' --Generating a simple password
BEGIN
IF (@len%2) = 0 --Appending a random vowel to @password
SET @password = @password + SUBSTRING(@bitmap,CONVERT(int,ROUND(1 + (RAND() * (5)),0)),1)
ELSE --Appending a random alphabet
SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0))
END
ELSE --Generating a complex password
BEGIN
SET @type = ROUND(1 + (RAND() * (3)),0)
IF @type = 1 --Appending a random lower case alphabet to @password
SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0))
ELSE IF @type = 2 --Appending a random upper case alphabet to @password
SET @password = @password + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @type = 3 --Appending a random number between 0 and 9 to @password
SET @password = @password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @type = 4 --Appending a random special character to @password
SET @password = @password + CHAR(ROUND(33 + (RAND() * (13)),0))
END
SET @len = @len - 1
END
SELECT @password --Here's the result
END
]]> كاتب الموضوع : AhmedEssawy

 الشرح ضمن الكود :


كود :
CREATE PROC random_password
(
@len int = 8, --Length of the password to be generated
@password_type char(7) = 'simple'
--Default is to generate a simple password with lowecase letters.
--Pass anything other than 'simple' to generate a complex password.
--The complex password includes numbers, special characters, upper case and lower case letters
)
AS
/***********************************************
Examples:
To generate a simple password with a length of 8 characters:
EXEC random_password
To generate a simple password with 6 characters:
EXEC random_password 6
To generate a complex password with 8 characters:
EXEC random_password @Password_type = 'complex'
To generate a comples password with 6 characters:
EXEC random_password 6, 'complex'
***********************************************/
BEGIN
DECLARE @password varchar(25), @type tinyint, @bitmap char(6)
SET @password=''
SET @bitmap = 'uaeioy'
--@bitmap contains all the vowels, which are a, e, i, o, u and y. These vowels are used to generate slightly readable/rememberable simple passwords
WHILE @len > 0
BEGIN
IF @password_type = 'simple' --Generating a simple password
BEGIN
IF (@len%2) = 0 --Appending a random vowel to @password
SET @password = @password + SUBSTRING(@bitmap,CONVERT(int,ROUND(1 + (RAND() * (5)),0)),1)
ELSE --Appending a random alphabet
SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0))
END
ELSE --Generating a complex password
BEGIN
SET @type = ROUND(1 + (RAND() * (3)),0)
IF @type = 1 --Appending a random lower case alphabet to @password
SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0))
ELSE IF @type = 2 --Appending a random upper case alphabet to @password
SET @password = @password + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @type = 3 --Appending a random number between 0 and 9 to @password
SET @password = @password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @type = 4 --Appending a random special character to @password
SET @password = @password + CHAR(ROUND(33 + (RAND() * (13)),0))
END
SET @len = @len - 1
END
SELECT @password --Here's the result
END
]]> <![CDATA[لدمج بيانات كافة الصفوف في عمود في صف واحد فقط]]> https://vb4arb.com/vb/showthread.php?tid=6008 Wed, 17 Oct 2012 10:10:10 +0000 Raggi Tech]]> https://vb4arb.com/vb/showthread.php?tid=6008 كاتب الموضوع : AhmedEssawy
كود :
USE pubs
GO
DECLARE @title_ids varchar(150), @delimiter char
SET @delimiter = ','
SELECT @title_ids = COALESCE(@title_ids + @delimiter, '') + title_id FROM titles
SELECT @title_ids AS
[List of Title IDs]
]]> كاتب الموضوع : AhmedEssawy
كود :
USE pubs
GO
DECLARE @title_ids varchar(150), @delimiter char
SET @delimiter = ','
SELECT @title_ids = COALESCE(@title_ids + @delimiter, '') + title_id FROM titles
SELECT @title_ids AS
[List of Title IDs]
]]> <![CDATA[لدمج أكثر من قاعدة بيانات]]> https://vb4arb.com/vb/showthread.php?tid=6007 Wed, 17 Oct 2012 10:09:51 +0000 Raggi Tech]]> https://vb4arb.com/vb/showthread.php?tid=6007 كاتب الموضوع : AhmedEssawy
كود :
-- Code
select * from BD1..Tabla1
inner join BD2..Tabla2
On BD1..Tabla1.Camp=
BD2..Tala2.Camp COLLATE Compatibility_52_409_30003
/*
Notes
----------------------------------------------------------
Title: SQL function to find invalid chars in a
string
Language: SQL
Description: Searches a string for invalid characters
Author: Jonathan Kingaby
*/
-- Code
CREATE FUNCTION DBO.IsInvalidString (@DNIS VARCHAR(50))
RETURNS bit AS
BEGIN
DECLARE @CNT INT
DECLARE @CHAR CHAR
DECLARE @IsInvalid BIT
SET @IsInvalid = 0
SET @CNT = 1
SET @CHAR = SUBSTRING(@DNIS, @CNT, 1)
WHILE @CHAR<>''
BEGIN
SELECT
@IsInvalid = 1
WHERE
@CHAR not IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','0','1','2','3','4','5','6','7','8','9')
IF @IsInvalid = 1
BREAK
SET @CNT = @CNT + 1
SET @CHAR = SUBSTRING(@DNIS, @CNT, 1)
END
RETURN @IsInvalid
END
Usage
SELECT
*
FROM tblfoo
WHERE dbo.IsInvalidString(stringcol)=1
/*
Notes
I wrote this to find dodgy characters in a field. There may well be a better way of doing it, if so please let me know.
*/
]]> كاتب الموضوع : AhmedEssawy
كود :
-- Code
select * from BD1..Tabla1
inner join BD2..Tabla2
On BD1..Tabla1.Camp=
BD2..Tala2.Camp COLLATE Compatibility_52_409_30003
/*
Notes
----------------------------------------------------------
Title: SQL function to find invalid chars in a
string
Language: SQL
Description: Searches a string for invalid characters
Author: Jonathan Kingaby
*/
-- Code
CREATE FUNCTION DBO.IsInvalidString (@DNIS VARCHAR(50))
RETURNS bit AS
BEGIN
DECLARE @CNT INT
DECLARE @CHAR CHAR
DECLARE @IsInvalid BIT
SET @IsInvalid = 0
SET @CNT = 1
SET @CHAR = SUBSTRING(@DNIS, @CNT, 1)
WHILE @CHAR<>''
BEGIN
SELECT
@IsInvalid = 1
WHERE
@CHAR not IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','0','1','2','3','4','5','6','7','8','9')
IF @IsInvalid = 1
BREAK
SET @CNT = @CNT + 1
SET @CHAR = SUBSTRING(@DNIS, @CNT, 1)
END
RETURN @IsInvalid
END
Usage
SELECT
*
FROM tblfoo
WHERE dbo.IsInvalidString(stringcol)=1
/*
Notes
I wrote this to find dodgy characters in a field. There may well be a better way of doing it, if so please let me know.
*/
]]> <![CDATA[لاسترجاع محتوايات بعض الاعمده]]> https://vb4arb.com/vb/showthread.php?tid=6006 Wed, 17 Oct 2012 10:09:30 +0000 Raggi Tech]]> https://vb4arb.com/vb/showthread.php?tid=6006 كاتب الموضوع : AhmedEssawy

 لاسترجاع محتوايات بعض الاعمده مثلا الاسم الاول و الاسم الاخير من جدول Persons ؛ نستعمل جمله استعلام مثل هذه :

[FONT=&quot]SELECT LastName,FirstName FROM Persons[/FONT]
]]> كاتب الموضوع : AhmedEssawy

 لاسترجاع محتوايات بعض الاعمده مثلا الاسم الاول و الاسم الاخير من جدول Persons ؛ نستعمل جمله استعلام مثل هذه :

[FONT=&quot]SELECT LastName,FirstName FROM Persons[/FONT]
]]> <![CDATA[لارسال فاكس باستخدام MS Word من خلال SQL]]> https://vb4arb.com/vb/showthread.php?tid=6005 Wed, 17 Oct 2012 10:09:06 +0000 Raggi Tech]]> https://vb4arb.com/vb/showthread.php?tid=6005 كاتب الموضوع : AhmedEssawy
كود :
/*Sql Server OLE Automation example by Giuseppe Dimauro 04/2000. Many Many Thanx to Him and all other developers....*/
DECLARE @WordDocument int
DECLARE @WordApplication int
DECLARE @Content int
DECLARE @visible int
DECLARE @hr int
DECLARE @text varchar(4096)
-- Set WordDocument = CreateObject("Word.Document")
EXEC @hr = sp_OACreate 'word.Document', @WordDocument OUT
-- Set Application = WordDocument.Application
IF @hr = 0
EXEC @hr = sp_OAGetProperty @WordDocument, 'Application', @WordApplication OUT
-- Set Content = WordDocument.Content
IF @hr = 0
EXEC @hr = sp_OAGetProperty @WordDocument, 'Content', @Content OUT
-- Content.Text = "Word Document " + vbNewLine + "generated by SQL Server"
IF @hr = 0

BEGIN
set @text = 'Word Document' + char(10) + 'generated by SQL Server'
EXEC @hr = sp_OASetProperty @Content, 'Text', @text
END
-- WordApplication.Visible = True
IF @hr = 0

BEGIN
EXEC @hr = sp_OASetProperty @WordApplication, 'Visible', 1
waitfor delay '00:00:10'
END
-- WordDocument.SendFax "", "Send a fax from SQL Server"
IF @hr = 0
EXEC @hr = sp_OAMethod @WordDocument, 'SendFax', NULL, '', 'Invio fax da SQL Server'
IF @hr <> 0

BEGIN
print "ERROR OCCURRED: " + cast(@hr as varchar(128))
RETURN
END
]]> كاتب الموضوع : AhmedEssawy
كود :
/*Sql Server OLE Automation example by Giuseppe Dimauro 04/2000. Many Many Thanx to Him and all other developers....*/
DECLARE @WordDocument int
DECLARE @WordApplication int
DECLARE @Content int
DECLARE @visible int
DECLARE @hr int
DECLARE @text varchar(4096)
-- Set WordDocument = CreateObject("Word.Document")
EXEC @hr = sp_OACreate 'word.Document', @WordDocument OUT
-- Set Application = WordDocument.Application
IF @hr = 0
EXEC @hr = sp_OAGetProperty @WordDocument, 'Application', @WordApplication OUT
-- Set Content = WordDocument.Content
IF @hr = 0
EXEC @hr = sp_OAGetProperty @WordDocument, 'Content', @Content OUT
-- Content.Text = "Word Document " + vbNewLine + "generated by SQL Server"
IF @hr = 0

BEGIN
set @text = 'Word Document' + char(10) + 'generated by SQL Server'
EXEC @hr = sp_OASetProperty @Content, 'Text', @text
END
-- WordApplication.Visible = True
IF @hr = 0

BEGIN
EXEC @hr = sp_OASetProperty @WordApplication, 'Visible', 1
waitfor delay '00:00:10'
END
-- WordDocument.SendFax "", "Send a fax from SQL Server"
IF @hr = 0
EXEC @hr = sp_OAMethod @WordDocument, 'SendFax', NULL, '', 'Invio fax da SQL Server'
IF @hr <> 0

BEGIN
print "ERROR OCCURRED: " + cast(@hr as varchar(128))
RETURN
END
]]> <![CDATA[كيفيه استخدام ال Any]]> https://vb4arb.com/vb/showthread.php?tid=6004 Wed, 17 Oct 2012 10:08:45 +0000 Raggi Tech]]> https://vb4arb.com/vb/showthread.php?tid=6004 كاتب الموضوع : AhmedEssawy
في المثال التالي سوف يتم الاستعلام عن كل الاسماء من جدول

Employee

بشرط ان يكون راتب الفرد اصغر من متوسط الرواتب

averageSalary

الموجوده في

جدول

job


كود :
[align=left][color=#990000]1[/color][color=black]>[/color][FONT=Courier New]
[color=#990000]2[/color]> create [color=black]table employee([/color]
[color=#990000]3[/color][color=black]> ID int,[/color]
[color=#990000]4[/color][color=black]> name nvarchar ([/color][color=#990000]10[/color][color=black]),[/color]
[color=#990000]5[/color]> salary int [color=black])[/color]
[color=#990000]6[/color][color=black]> GO[/color]
[color=#990000]1[/color][color=black]>[/color]
[color=#990000]2[/color]> create [color=black]table job([/color]
[color=#990000]3[/color][color=black]> ID int,[/color]
[color=#990000]4[/color][color=black]> title nvarchar ([/color][color=#990000]10[/color][color=black]),[/color]
[color=#990000]5[/color]> averageSalary int[color=black])[/color]
[color=#990000]6[/color][color=black]> GO[/color][/FONT]
[color=#990000]1[/color][color=black]>[/color][FONT=Courier New]
[color=#990000]2[/color][color=black]>[/color]
[color=#990000]3[/color]> select * from [color=black]employee;[/color]
[color=#990000]4[/color][color=black]> GO[/color]
[color=black]ID name salary[/color]
[color=black]----------- ---------- -----------[/color]
[color=#990000]1 [/color][color=black]Jason [/color][color=#990000]1234[/color]
[color=#990000]2 [/color][color=black]Robert [/color][color=#990000]4321[/color]
[color=#990000]3 [/color][color=black]Celia [/color][color=#990000]5432[/color]
[color=#990000]4 [/color][color=black]Linda [/color][color=#990000]3456[/color]
[color=#990000]5 [/color][color=black]David [/color][color=#990000]7654[/color]
[color=#990000]6 [/color][color=black]James [/color][color=#990000]4567[/color]
[color=#990000]7 [/color][color=black]Alison [/color][color=#990000]8744[/color]
[color=#990000]8 [/color][color=black]Chris [/color][color=#990000]9875[/color]
[color=#990000]9 [/color][color=black]Mary [/color][color=#990000]2345[/color]

[color=black]([/color][color=#990000]9 [/color][color=black]rows affected)[/color]
[color=#990000]1[/color]> select * from [color=black]job;[/color]
[color=#990000]2[/color][color=black]> GO[/color]
[color=black]ID title averageSalary[/color]
[color=black]----------- ---------- -------------[/color]
[color=#990000]1 [/color][color=black]Developer [/color][color=#990000]3000[/color]
[color=#990000]2 [/color][color=black]Tester [/color][color=#990000]4000[/color]
[color=#990000]3 [/color][color=black]Designer [/color][color=#990000]5000[/color]
[color=#990000]4 [/color][color=black]Programmer [/color][color=#990000]6000[/color]

[color=black]([/color][color=#990000]4 [/color][color=black]rows affected)[/color][/FONT]
[color=#990000]1[/color][color=black]>[/color][FONT=Courier New]
[color=#990000]2[/color][color=black]>[/color]
[color=#990000]3[/color]> -- If [color=black]your subquery returns a scalar value, you can use a comparison operator,[/color]
[color=#990000]4[/color][color=black]>[/color]
[color=#990000]5[/color]> SELECT [color=black]e.ID,e.name[/color]
[color=#990000]6[/color]> FROM [color=black]Employee e[/color]
[color=#990000]7[/color]> WHERE e.salary > ANY (SELECT averageSalary FROM [color=black]job j)[/color]
[color=#990000]8[/color][color=black]> GO[/color]
[color=black]ID name[/color]
[color=black]----------- ----------[/color]
[color=#990000]2 [/color][color=black]Robert[/color]
[color=#990000]3 [/color][color=black]Celia[/color]
[color=#990000]4 [/color][color=black]Linda[/color]
[color=#990000]5 [/color][color=black]David[/color]
[color=#990000]6 [/color][color=black]James[/color]
[color=#990000]7 [/color][color=black]Alison[/color]
[color=#990000]8 [/color][color=black]Chris[/color]

[color=black]([/color][color=#990000]7 [/color][color=black]rows affected)[/color][/FONT][/align]
]]> كاتب الموضوع : AhmedEssawy
في المثال التالي سوف يتم الاستعلام عن كل الاسماء من جدول

Employee

بشرط ان يكون راتب الفرد اصغر من متوسط الرواتب

averageSalary

الموجوده في

جدول

job


كود :
[align=left][color=#990000]1[/color][color=black]>[/color][FONT=Courier New]
[color=#990000]2[/color]> create [color=black]table employee([/color]
[color=#990000]3[/color][color=black]> ID int,[/color]
[color=#990000]4[/color][color=black]> name nvarchar ([/color][color=#990000]10[/color][color=black]),[/color]
[color=#990000]5[/color]> salary int [color=black])[/color]
[color=#990000]6[/color][color=black]> GO[/color]
[color=#990000]1[/color][color=black]>[/color]
[color=#990000]2[/color]> create [color=black]table job([/color]
[color=#990000]3[/color][color=black]> ID int,[/color]
[color=#990000]4[/color][color=black]> title nvarchar ([/color][color=#990000]10[/color][color=black]),[/color]
[color=#990000]5[/color]> averageSalary int[color=black])[/color]
[color=#990000]6[/color][color=black]> GO[/color][/FONT]
[color=#990000]1[/color][color=black]>[/color][FONT=Courier New]
[color=#990000]2[/color][color=black]>[/color]
[color=#990000]3[/color]> select * from [color=black]employee;[/color]
[color=#990000]4[/color][color=black]> GO[/color]
[color=black]ID name salary[/color]
[color=black]----------- ---------- -----------[/color]
[color=#990000]1 [/color][color=black]Jason [/color][color=#990000]1234[/color]
[color=#990000]2 [/color][color=black]Robert [/color][color=#990000]4321[/color]
[color=#990000]3 [/color][color=black]Celia [/color][color=#990000]5432[/color]
[color=#990000]4 [/color][color=black]Linda [/color][color=#990000]3456[/color]
[color=#990000]5 [/color][color=black]David [/color][color=#990000]7654[/color]
[color=#990000]6 [/color][color=black]James [/color][color=#990000]4567[/color]
[color=#990000]7 [/color][color=black]Alison [/color][color=#990000]8744[/color]
[color=#990000]8 [/color][color=black]Chris [/color][color=#990000]9875[/color]
[color=#990000]9 [/color][color=black]Mary [/color][color=#990000]2345[/color]

[color=black]([/color][color=#990000]9 [/color][color=black]rows affected)[/color]
[color=#990000]1[/color]> select * from [color=black]job;[/color]
[color=#990000]2[/color][color=black]> GO[/color]
[color=black]ID title averageSalary[/color]
[color=black]----------- ---------- -------------[/color]
[color=#990000]1 [/color][color=black]Developer [/color][color=#990000]3000[/color]
[color=#990000]2 [/color][color=black]Tester [/color][color=#990000]4000[/color]
[color=#990000]3 [/color][color=black]Designer [/color][color=#990000]5000[/color]
[color=#990000]4 [/color][color=black]Programmer [/color][color=#990000]6000[/color]

[color=black]([/color][color=#990000]4 [/color][color=black]rows affected)[/color][/FONT]
[color=#990000]1[/color][color=black]>[/color][FONT=Courier New]
[color=#990000]2[/color][color=black]>[/color]
[color=#990000]3[/color]> -- If [color=black]your subquery returns a scalar value, you can use a comparison operator,[/color]
[color=#990000]4[/color][color=black]>[/color]
[color=#990000]5[/color]> SELECT [color=black]e.ID,e.name[/color]
[color=#990000]6[/color]> FROM [color=black]Employee e[/color]
[color=#990000]7[/color]> WHERE e.salary > ANY (SELECT averageSalary FROM [color=black]job j)[/color]
[color=#990000]8[/color][color=black]> GO[/color]
[color=black]ID name[/color]
[color=black]----------- ----------[/color]
[color=#990000]2 [/color][color=black]Robert[/color]
[color=#990000]3 [/color][color=black]Celia[/color]
[color=#990000]4 [/color][color=black]Linda[/color]
[color=#990000]5 [/color][color=black]David[/color]
[color=#990000]6 [/color][color=black]James[/color]
[color=#990000]7 [/color][color=black]Alison[/color]
[color=#990000]8 [/color][color=black]Chris[/color]

[color=black]([/color][color=#990000]7 [/color][color=black]rows affected)[/color][/FONT][/align]
]]> <![CDATA[عمل جدول للتاريخ والوقت باستخدام intervals]]> https://vb4arb.com/vb/showthread.php?tid=6003 Wed, 17 Oct 2012 10:08:25 +0000 Raggi Tech]]> https://vb4arb.com/vb/showthread.php?tid=6003 كاتب الموضوع : AhmedEssawy

 الكود مشروح باستفاضة ضمن الخطوات .


كود :
CREATE FUNCTION dbo.DateTimeList (
@startDate AS DATETIME,
@endDate AS DATETIME,
@interval AS INT = 1, -- this is the interval per daypart default is 1
@daypart AS VARCHAR(10) = 'day' -- the default is by day
)
RETURNS @DateTimeTable TABLE (
DateList DATETIME
)
AS
BEGIN
/*******************************************************/
/* */
/* This function will return a table of date times */
/* based off of a specified part and */
/* interval. For instance, if you need a list of */
/* date times for every hour between a particular */
/* start and end date. The function supports */
/* day parts from year down to minute */
/*******************************************************/
/* */
/* Input Parameters: */
/* */
/* StartDate datetime Start Date of range. */
/* REQUIRED parameter. */
/* */
/* EndDate datetime End Date of range. */
/* REQUIRED parameter. */
/* */
/* Interval int Integer value representing */
/* the amount to increment */
/* defaults to 1. */
/* */
/* DayPart varchar String value representing*/
/* the day part to use */
/* valid values are: */
/* 'year' */
/* 'quarter' */
/* 'month' */
/* 'week' */
/* 'day' */
/* 'hour' */
/* 'minute' */
/* */
/*******************************************************/
/* */
/* Returns: */
/* */
/* table Table of date time values by day part*/
/* within the input time period. */
/* */
/*******************************************************/
/* */
/* Usage: */
/* */
/* exec DateTimeList */
/* @StartDate = 'mm/dd/yyyy', */
/* @EndDate = 'mm/dd/yyyy', */
/* @Interval = i */
/* @DayPart = 'day part needed' */
/* */
/*******************************************************/
DECLARE @lenDiff TINYINT
DECLARE @dateDiff INT
/*******************************************************/
/* */
/* Setup date difference based off of entered day part.*/
/* Use the length of the resulting date difference to */
/* determine how many digits will be used in the */
/* cross join. */
/* setup a table of digits for use in the cross join */
/* */
/*******************************************************/
SELECT @dateDiff = CASE @daypart
WHEN 'year' THEN DATEDIFF(yy,@startDate,@endDate)
WHEN 'quarter' THEN DATEDIFF(qq,@startDate,@endDate)
WHEN 'month' THEN DATEDIFF(mm,@startDate,@endDate)
]]> كاتب الموضوع : AhmedEssawy

 الكود مشروح باستفاضة ضمن الخطوات .


كود :
CREATE FUNCTION dbo.DateTimeList (
@startDate AS DATETIME,
@endDate AS DATETIME,
@interval AS INT = 1, -- this is the interval per daypart default is 1
@daypart AS VARCHAR(10) = 'day' -- the default is by day
)
RETURNS @DateTimeTable TABLE (
DateList DATETIME
)
AS
BEGIN
/*******************************************************/
/* */
/* This function will return a table of date times */
/* based off of a specified part and */
/* interval. For instance, if you need a list of */
/* date times for every hour between a particular */
/* start and end date. The function supports */
/* day parts from year down to minute */
/*******************************************************/
/* */
/* Input Parameters: */
/* */
/* StartDate datetime Start Date of range. */
/* REQUIRED parameter. */
/* */
/* EndDate datetime End Date of range. */
/* REQUIRED parameter. */
/* */
/* Interval int Integer value representing */
/* the amount to increment */
/* defaults to 1. */
/* */
/* DayPart varchar String value representing*/
/* the day part to use */
/* valid values are: */
/* 'year' */
/* 'quarter' */
/* 'month' */
/* 'week' */
/* 'day' */
/* 'hour' */
/* 'minute' */
/* */
/*******************************************************/
/* */
/* Returns: */
/* */
/* table Table of date time values by day part*/
/* within the input time period. */
/* */
/*******************************************************/
/* */
/* Usage: */
/* */
/* exec DateTimeList */
/* @StartDate = 'mm/dd/yyyy', */
/* @EndDate = 'mm/dd/yyyy', */
/* @Interval = i */
/* @DayPart = 'day part needed' */
/* */
/*******************************************************/
DECLARE @lenDiff TINYINT
DECLARE @dateDiff INT
/*******************************************************/
/* */
/* Setup date difference based off of entered day part.*/
/* Use the length of the resulting date difference to */
/* determine how many digits will be used in the */
/* cross join. */
/* setup a table of digits for use in the cross join */
/* */
/*******************************************************/
SELECT @dateDiff = CASE @daypart
WHEN 'year' THEN DATEDIFF(yy,@startDate,@endDate)
WHEN 'quarter' THEN DATEDIFF(qq,@startDate,@endDate)
WHEN 'month' THEN DATEDIFF(mm,@startDate,@endDate)
]]> <![CDATA[عرض جميع جداول قاعدة البيانات ، Indexes والجروبات]]> https://vb4arb.com/vb/showthread.php?tid=6002 Wed, 17 Oct 2012 10:08:01 +0000 Raggi Tech]]> https://vb4arb.com/vb/showthread.php?tid=6002 كاتب الموضوع : AhmedEssawy
كود :
select 'table_name'=object_name(i.id) ,i.indid
,'index_name'=i.name ,i.groupid
,'filegroup'=f.name ,'file_name'=d.physical_name
,'dataspace'=s.name from sys.sysindexes i
,sys.filegroups f ,sys.database_files d
,sys.data_spaces s
where objectproperty(i.id,'IsUserTable') = 1
and f.data_space_id = i.groupid
and f.data_space_id = d.data_space_id
and f.data_space_id = s.data_space_id
order by f.name,object_name(i.id),groupid
go
]]> كاتب الموضوع : AhmedEssawy
كود :
select 'table_name'=object_name(i.id) ,i.indid
,'index_name'=i.name ,i.groupid
,'filegroup'=f.name ,'file_name'=d.physical_name
,'dataspace'=s.name from sys.sysindexes i
,sys.filegroups f ,sys.database_files d
,sys.data_spaces s
where objectproperty(i.id,'IsUserTable') = 1
and f.data_space_id = i.groupid
and f.data_space_id = d.data_space_id
and f.data_space_id = s.data_space_id
order by f.name,object_name(i.id),groupid
go
]]>