Knowing if Data and Log files are on the same drive

SELECT SERVERPROPERTY('machinename') AS 'Server Name',
ISNULL(SERVERPROPERTY('instancename'), SERVERPROPERTY('machinename'))  AS 'Instance Name',
name,
drive_letter AS 'Drive Letter',
Comments, Path
FROM
(
(
SELECT DISTINCT
UPPER(LEFT(LTRIM(physical_name),2)) AS drive_letter,
REVERSE(RIGHT(REVERSE(physical_name),(LEN(physical_name)-CHARINDEX('\', REVERSE(physical_name),1))+1)) [Path],
N'Device holds both tempdb and user database objects' AS 'Comments',
DB_NAME(database_id) [name],
1 AS OrderBy
FROM MASTER.sys.master_files
WHERE LOWER(DB_NAME(database_id)) = 'tempdb'
AND UPPER(LEFT(LTRIM(physical_name),2)) IN
(
SELECT UPPER(LEFT(LTRIM(physical_name),2))
FROM MASTER.sys.master_files
WHERE LOWER(DB_NAME(database_id)) NOT IN (N'tempdb', N'master', N'msdb', N'adventureworks', N'adventureworksdw', N'model')
)
)
UNION
(
SELECT drive_letter, path,
N'Device holds both data and log objects' AS 'Comments', name,
2 AS OrderBy
FROM
(
SELECT drive_letter, name, Path
FROM
(
SELECT DISTINCT UPPER(LEFT(LTRIM(physical_name),2)) AS drive_letter,
REVERSE(RIGHT(REVERSE(physical_name),(LEN(physical_name)-CHARINDEX('\', REVERSE(physical_name),1))+1)) [Path],
TYPE, DB_NAME(database_id) [name]
FROM MASTER.sys.master_files
WHERE LOWER(DB_NAME(database_id)) NOT IN (N'master', N'msdb', N'tempdb', N'adventureworks', N'adventureworksdw', N'model')
) a
GROUP BY drive_letter, a.name, path
HAVING COUNT(1) >= 2
) Drives
)
) Drive
ORDER BY OrderBy, drive_letter

Via.

Windows File Junctions, Symbolic Links and Hard Links

The Windows NTFS file system has supported some form of file and directory pointing since Windows 2000. Unfortunately each revision of Windows until Windows Vista has used a different method of implementing these pointers. So for this article I will just focus on Windows Vista and the identical Windows 7/8 implementations.

In Windows what is the difference between a short-cut, a symbolic link (sym-link) and a hard link?

A short cut is a file that points to another file. It is an antiquated pointing system from the Windows 95 era that many programs do not recognise. Short-cuts do not only use up space on the hard drive, they also break and linger behind after the target has been deleted, renamed or moved.

A symbolic link is like a short-cut but instead of being saved as a file it is registered to the hard drive partition. It does not use any disk space and all programs recognise both the link and the target. A symbolic link can point to any file or folder either locally on the computer or over a network using a SMB path.

A file hard link is a little different and can not be used over multiple partitions meaning you can not have a link on drive C: pointing to a file on drive D:. A file hard link points to and duplicates a target as a mirrored copy but the copy does not use any additional space on the hard drive partition. So 2 hard links that mirrored a 1 GB file would in total only use 1 GB on the partition rather than 3 GB. Importantly if either the hard links or the target are deleted the other links retain the data. Changes to the content of either the target or the links automatically propagate to all other items.

A junction behaves like is a hard link for directories but unlike file hard links you can create junctions that span multiple partitions. Again a directory junction and its content is stored on the hard drive partition but they do not use any additional space. Any changes to the content within either the target or the links will automatically propagate except where the target directory is deleted or renamed. In that case all hard links that point to the target will break and linger on the partition.

Microsoft Windows Vista, 7 and 8 use the command line program mklink to create links. It has 3 arguments and requires both a link name and target.

mklink /D /H /J [LINK NAME] [TARGET]

There are 4 modes for mklink which counteract each other so you can only use at most a single argument.

Supplying no arguments creates file symbolic link which is a pointer to a file.

/D creates a directory symbolic link which is a pointer to a directory.

/H creates a file hard link and is best used in situations where you need multiple mirrors of a file.

/J creates a directory junction which is a directory link that mirrors a the target directory over the same or on a different hard drive partition.

The word mirrors in this context means the live duplication of the target and the links. Any changes to the structure or the content of any files or any directories will propagate instantly to all links and to the target.

mklink /J WindowsVista C:\Windows

This command would create a directory junction WindowsVista linking (pointing) to the directory C:\Windows.


Via.

Walking the Table Hierarchy in Microsoft SQL Server Database

When you need to manage a set of tables in Microsoft SQL Server, it’s good to know the required order of operations. The order could be hard-coded into the process but such approaches tend to fail when the database schema evolves. Instead, I prefer to use the catalog view named [sys].[foreign_keys] to discover the relationships between tables dynamically. Long ago, I wrote a function called [LoadLevels] that I’ve used in hundreds of processes to make them reusable and more resilient. The code for that function is shown in Listing 1:

-- ==========================================================================
-- Description: Get the load levels by tracing foreign keys in the database.
-- License:     Creative Commons (Free / Public Domain)
-- Rights:      This work (Linchpin People LLC Database Load Levels Function,
--              by W. Kevin Hazzard), identified by Linchpin People LLC, is
--              free of known copyright restrictions.
-- Warranties:  This code comes with no implicit or explicit warranties.
--              Linchpin People LLC and W. Kevin Hazzard are not responsible
--              for the use of this work or its derivatives.
-- ==========================================================================
CREATE FUNCTION [dbo].[LoadLevels]()
RETURNS @results TABLE
(
[SchemaName] SYSNAME,
[TableName] SYSNAME,
[LoadLevel] INT
)
AS
BEGIN
WITH [key_info] AS
(
SELECT
[parent_object_id] AS [from_table_id],
[referenced_object_id] AS [to_table_id]
FROM [sys].[foreign_keys]
WHERE
[parent_object_id] <> [referenced_object_id]
AND [is_disabled] = 0
),
[level_info] AS
(
SELECT -- anchor part
[st].[object_id] AS [to_table_id],
0 AS [LoadLevel]
FROM [sys].[tables] AS [st]
LEFT OUTER JOIN [key_info] AS [ki] ON
[st].[object_id] = [ki].[from_table_id]
WHERE [ki].[from_table_id] IS NULL
UNION ALL
SELECT -- recursive part
[ki].[from_table_id],
[li].[LoadLevel] + 1
FROM [key_info] AS [ki]
INNER JOIN [level_info] AS [li] ON
[ki].[to_table_id] = [li].[to_table_id]
)
INSERT @results
SELECT
OBJECT_SCHEMA_NAME([to_table_id]) AS [SchemaName],
OBJECT_NAME([to_table_id]) AS [TableName],
MAX([LoadLevel]) AS [LoadLevel]
FROM [level_info]
GROUP BY [to_table_id];
RETURN
END
GO

Via.

Programmatically Retrieve Printer Capabilities

Sub DebugGetBinList(strName As String)

    ' https://msdn.microsoft.com/en-us/library/bb258176%28v=office.12%29.aspx
    
    ' Uses the DeviceCapabilities API function to display a
    ' message box with the name of the default printer and a
    ' list of the paper bins it supports.
 
    ' Boso's version: output su Debug.Print()

    Dim lngBinCount As Long
    Dim lngCounter As Long
    Dim hPrinter As Long
    Dim strDeviceName As String
    Dim strDevicePort As String
    Dim strBinNamesList As String
    Dim strBinName As String
    Dim intLength As Integer
    Dim strMsg As String
    Dim aintNumBin() As Integer
    Dim riga As String
    riga = String(Len(strName) + 25, "-")
    
    On Error GoTo GetBinList_Err
    
    ' Get name and port of the default printer.
    strDeviceName = Application.Printers(strName).DeviceName
    strDevicePort = Application.Printers(strName).Port
    
    ' Get count of paper bin names supported by the printer.
    lngBinCount = DeviceCapabilities(lpsDeviceName:=strDeviceName, _
                                     lpPort:=strDevicePort, _
                                     iIndex:=DC_BINNAMES, _
                                     lpOutput:=ByVal vbNullString, _
                                     lpDevMode:=DEFAULT_VALUES)
    
    ' Re-dimension the array to count of paper bins.
    ReDim aintNumBin(1 To lngBinCount)
    
    ' Pad variable to accept 24 bytes for each bin name.
    strBinNamesList = String(Number:=24 * lngBinCount, Character:=0)

    ' Get string buffer of paper bin names supported by the printer.
    lngBinCount = DeviceCapabilities(lpsDeviceName:=strDeviceName, _
                                     lpPort:=strDevicePort, _
                                     iIndex:=DC_BINNAMES, _
                                     lpOutput:=ByVal strBinNamesList, _
                                     lpDevMode:=DEFAULT_VALUES)
        
    ' Get array of paper bin numbers supported by the printer.
    lngBinCount = DeviceCapabilities(lpsDeviceName:=strDeviceName, _
                                     lpPort:=strDevicePort, _
                                     iIndex:=DC_BINS, _
                                     lpOutput:=aintNumBin(1), _
                                     lpDevMode:=0)
    
    ' List available paper bin names.
    strMsg = ""
    strMsg = strMsg & vbCrLf
    strMsg = strMsg & "Paper bins available for " & strDeviceName & vbCrLf
    strMsg = strMsg & vbCrLf & riga & vbCrLf
    strMsg = strMsg & "ID  Name"
    strMsg = strMsg & vbCrLf & riga
    
    For lngCounter = 1 To lngBinCount
        
        ' Parse a paper bin name from string buffer.
        strBinName = Mid(String:=strBinNamesList, _
                         Start:=24 * (lngCounter - 1) + 1, _
                         Length:=24)
        
        intLength = VBA.InStr(1, strBinName, Chr(0)) - 1
        
        strBinName = Left(strBinName, intLength)

        ' Add bin name and number to text string for message box.
        strMsg = strMsg & vbCrLf & aintNumBin(lngCounter) & vbTab & strBinName
        
    Next lngCounter
    
    ' Show paper bin numbers and names in message box.
    Debug.Print strMsg
    Debug.Print riga
    
GetBinList_End:
    Exit Sub

GetBinList_Err:
    MsgBox Prompt:=Err.Description, Buttons:=vbCritical & vbOKOnly, _
        Title:="Error Number " & Err.Number & " Occurred"
    Resume GetBinList_End

End Sub

Trovato qui.

NZ function in SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
-------------------------------------------------------------------------------
Author:      Francesco Bosetti
Create date: 2015-04-08
Description: come la funzione NZ() di MS Access: 
             se il @value è NULL o '', ritorna @valueIfNull
-------------------------------------------------------------------------------
*/

CREATE FUNCTION NZ
(
   @value AS nvarchar(MAX)
 , @valueIfNull AS nvarchar(MAX)
)
RETURNS nvarchar(MAX)
AS
BEGIN
 DECLARE @res AS nvarchar(MAX)
 SET @res = CASE WHEN ISNULL(@value, '') = '' THEN @valueIfNull ELSE @value END
 RETURN @res
END
GO

Some tests:

DECLARE @A AS NVARCHAR(100) = 'AAAA'
DECLARE @B AS NVARCHAR(100) = ''
DECLARE @C AS NVARCHAR(100) = NULL

SELECT @A, @B, @C
SELECT dbo.NZ(@A, '1'), dbo.NZ(@B, '2'), dbo.NZ(@C, '3')

Results:

a          b          c
---------- ---------- ----------
AAAA                  NULL


a          b          c
---------- ---------- ----------
AAAA       2          3

Another version here.