Determining if a Folder Exists Using xp_cmdshell

The code below creates a stored proc that will report if a passed-in Windows folder exists or not. While it avoids using the undocumented xp_fileexist system proc, it uses xp_cmdshell, which in many places is the first thing that gets disabled because of the obvious security ramifications. Still, it's a nice hack if you're working in the file system a lot.

IF OBJECT_ID('dbo.usp_FolderExist') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.usp_FolderExist
END
 
GO
 
CREATE PROCEDURE dbo.usp_FolderExist (@FullPathName NVARCHAR(255),
                                      @Exists       TINYINT OUTPUT)
AS
BEGIN
    SET NOCOUNT ON
   
    -- Remove any trailing slash.
    DECLARE @TrailingSlash NVARCHAR(255)
    SET @TrailingSlash = REVERSE(@FullPathName)
    
    IF '\' = LEFT(@TrailingSlash, 1) 
        SET @TrailingSlash = SUBSTRING(@TrailingSlash, 2, LEN(@FullPathName) + 1)
  
    SET @FullPathName = REVERSE(@TrailingSlash)
   
    -- Build and exec the command.
    DECLARE @Cmd NVARCHAR(4000)
    SET @Cmd = 'dir ' + @FullPathName
    SET @Exists = 0
    CREATE TABLE #dir (output NVARCHAR(4000))
 
    INSERT INTO #dir
        EXEC master..xp_cmdshell @Cmd
 
    IF EXISTS (SELECT * FROM #dir WHERE output LIKE '%' + @FullPathName + '%')
        SET @Exists = 1
      
    -- Done.
    DROP TABLE #dir
END
 

/* Test Code
 
set nocount on
declare @bExists int   set @bExists = 0
exec usp_FolderExist 'C:\Temp',  @bExists OUTPUT
print @bExists
exec usp_FolderExist 'C:\Temp\', @bExists OUTPUT
print @bExists
 
*/
 

Displaying Database and Server Principals

A quick example of using the system views to look at the server principals and the database principals. First, let's look at the database principals:

SELECT  dbp.class_desc                       AS GrantedObjectType,
        CASE dbp.class_desc
            WHEN 'DATABASE'         THEN DB_NAME    (dbp.major_id)
            WHEN 'OBJECT_OR_COLUMN' THEN OBJECT_NAME(dbp.major_id)
            WHEN 'SCHEMA'           THEN SCHEMA_NAME(dbp.major_id)
        END                                  AS GrantedObjectName,
        COL_NAME(dbp.major_id, dbp.minor_id) AS GrantedColumnName,
        dbp.state_desc                       AS State,
        dbp.permission_name                  AS Permission,
        dpGrantor.type_desc                  AS GrantorType,
        dpGrantor.name                       AS Grantor,
        dpGrantee.type_desc                  AS GranteeType,
        dpGrantee.name                       AS Grantee,
        dpRole.name                          AS GranteeRole
   FROM sys.database_permissions                                     dbp
   JOIN sys.database_principals                                      dpGrantor
     ON dbp.grantor_principal_id = dpGrantor.principal_id
   JOIN sys.database_principals                                      dpGrantee
     ON dbp.grantee_principal_id = dpGrantee.principal_id
   JOIN sys.database_role_members                                    droGrantee
     ON dbp.grantee_principal_id = droGrantee.member_principal_id
   JOIN sys.database_principals                                      dpRole
     ON droGrantee.role_principal_id = dpRole.principal_id
   JOIN sys.objects                                                  o
     ON dbp.major_id = o.object_id
  ORDER BY GrantedObjectType,
        GrantedObjectName,
        Permission,
        State

The server principals are done in a similar way:

        
SELECT  svp.class_desc            AS GrantedObjectType,
        SCHEMA_NAME(svp.major_id) AS GrantedObjectName,
        svp.state_desc            AS State,
        svp.permission_name       AS Permission,
        spGrantor.type_desc       AS GrantorType,
        spGrantor.name            AS Grantor,
        spGrantee.type_desc       AS GranteeType,
        spGrantee.name            AS Grantee,
        spRole.name               AS GranteeRole
   FROM sys.server_permissions                                     svp
   JOIN sys.server_principals                                      spGrantor
     ON svp.grantor_principal_id = spGrantor.principal_id
   LEFT JOIN sys.server_principals                                 spGrantee
     ON svp.grantee_principal_id = spGrantee.principal_id
   LEFT JOIN sys.server_role_members                               sroGrantee
     ON svp.grantee_principal_id = sroGrantee.member_principal_id
   LEFT JOIN sys.server_principals                                 spRole
     ON sroGrantee.role_principal_id = spRole.principal_id
   LEFT JOIN sys.objects                                           o
     ON svp.major_id = o.object_id
  ORDER BY GrantedObjectType,
           GrantedObjectName,
           Permission,
           State

ENABLE/DISABLE TRIGGER must follow a semicolon

Can't get an ENABLE or DISABLE TRIGGER to even parse, much less execute? This might be the cause,

Due to a Microsoft glitch, ENABLE and DISABLE are not yet keywords. This means for them to be recognized as the beginning of a statement, the preceeding statement must end with a semi-colon. But putting it on the front of the offending line is clearer, to me:

;DISABLE TRIGGER dbo.trDoSomething ON MyTable

For more info, see: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=307937&wa=wsignin1.0

Using PIVOT - An (honest!) Simple Example

The concept of the PIVOT keyword is pretty simple: rotate the results so that the rows and columns are transposed. But the syntax is, well, kinda weird. This example uses PIVOT in the simplest way, to make it easier to pick through the code and see what's going on.

First let's create the example table, which contains one row for each girl. In our admittedly skewed sample set, all girls have cats, and there are only five girl names represented: Ada, Bea, Cat, Dot, and Eve. (If anyone knows of a three-letter girl's name beginning with "C", I'd be obliged.)

-- Drop and re-create the table. 
IF OBJECT_ID('dbo.Girl') IS NOT NULL DROP TABLE dbo.Girl

CREATE TABLE dbo.Girl
(
    GirlID INT IDENTITY(1,1), 
    Name       NVARCHAR(50),
    CatCount   TINYINT              
)

Now let's add some reasonable (except for one "crazy cat lady", apparently) data to the table.

-- Populate the table.  
INSERT INTO dbo.Girl
    (Name, CatCount)
VALUES
    ('DOT', 9), ('CAT', 3), ('CAT', 1), ('ANN', 3),
    ('ANN', 3), ('BEA', 3), ('EVE', 4), ('EVE', 3),
    ('EVE', 3), ('EVE', 4), ('EVE', 4), ('ANN', 3),
    ('EVE', 3), ('BEA', 5), ('EVE', 3), ('ANN', 1),
    ('EVE', 1), ('EVE', 3), ('EVE', 3), ('CAT', 103),
    ('BEA', 2), ('EVE', 3), ('CAT', 3), ('DOT', 2),
    ('DOT', 4), ('EVE', 3), ('DOT', 9), ('BEA', 2)

One of the common things we do with data like this is to create subtotals by value. In this case, we can find the total number of cats by girl-name.

-- Group by girls' name to see which name has the most cats. 
-- This displays the girls' names as rows.
SELECT Name           AS 'Girl''s Name',
       SUM(CatCount)  AS 'Total Cats'
  FROM dbo.Girl
 GROUP BY Name

The results would look like this:

-- Girl's Name Total Cats
   ANN          10
   BEA          12
   CAT          110
   DOT          24
   EVE          37

But by using the PIVOT keyword, we can also display the data with the girls' names as the columns. If you think about it, that's pretty cool: it turns data into metadata.

-- Get the same data, but with the girls' names as columns. 
SELECT  ANN  AS 'Total Cats: Ann',
        BEA  AS 'Total Cats: Bea',
        CAT  AS 'Total Cats: Cat',
        DOT  AS 'Total Cats: Dot',
        EVE  AS 'Total Cats: Eve'
  FROM
    (SELECT Name,
            CatCount
       FROM dbo.Girl) AS tSource
PIVOT
(
    SUM(CatCount)
    FOR Name IN (ANN, BEA, CAT, DOT, EVE)
) AS tPivoted

The results would look like this:

-- Total Cats: Ann    Total Cats: Bea    Total Cats: Cat    Total Cats: Dot    Total Cats: Eve
   10                 12                 110                24                 37

Of course, this capability comes at a price: you have to explicitly list the five girl names, which means you have to know what the values are in advance. Often, that's not a big problem: for example, the months of the year.