Get a List of Fixed Hard Drive and Free Space on Server

If I do not have access to OS I use the following Stored Procedure to figure out the number of fixed drives (hard drive) a system has along with free space on each of those drives. 

EXEC master..xp_fixeddrives

Get Directory Structure using Extended Stored Procedure xp_dirtree

Well, there is one undocumented stored procedure exists which can do the same. However, please be vary to use any undocumented procedures.

xp_dirtree 'C:\Windows'


Execution of the above stored procedure will give following result. If you prefer you can insert the data in the temptable and use the same for further use.

Here is the quick script which will insert the data into the temptable and retrieve from the same.

CREATE TABLE #TempTable (Subdirectory VARCHAR(512), Depth INT);
INSERT INTO #TempTable (Subdirectory, Depth)
EXEC xp_dirtree 'C:\Windows'
SELECT Subdirectory, Depth
FROM #TempTable;
DROP TABLE #TempTable;

Handling special characters with FOR XML PATH('')

Since SQL Server 2005, we’ve been able to use FOR XML PATH('') to do string concatenation. I’ve blogged about it before several times. But I don’t think I’ve blogged about the fact that it all goes a bit wrong if you have special characters in the strings you’re concatenating.
Generally, I don’t even worry about this. I should, but I don’t, particularly when the solution is so easy.
Suppose I want to concatenate the list of user databases on my system, in alphabetical order. Easy: Note the lack of

select
  stuff(
     (select ', ' + name -- Note the lack of column name
     from sys.databases
     where database_id > 4
     order by name
     for xml path('')
     )
   , 1, 2, '') as namelist;

This is easy, and it just works. The STUFF command lets me strip the leading comma and space, and my list of database names is done.
But if I wanted to list the names of database with triangular brackets around them… then that’s bit more complicated.

select
  stuff(
     (select ', <' + name + '>'
     from sys.databases
     where database_id > 4
     order by name
     for xml path('')
     )
   , 1, 2, '') as namelist;

It still runs, but I my results don’t show the triangular brackets, it shows <databasename>, <databasename2>. It’s not what I want to see. I effectively need to HTMLDecode the results – but T-SQL doesn’t have a function like that.
However, if I actually make my FOR XML call return actual well-formed XML, then I can extract the contents out, and it returns the data to me in its original (correct) form.

select
  stuff(
     (select ', <' + name + '>'
     from sys.databases
     where database_id > 4
     order by name
     for xml path(''), root('MyString'), type      ).value('/MyString[1]','varchar(max)')
   , 1, 2, '') as namelist;

 OR

select

 stuff(

    (select ', <' + name + '>'

    from sys.databases

    where database_id > 4

    order by name

    for xml path(''), type

    ).value('(./text())[1]','varchar(max)')

  , 1, 2, '') as namelist;

To be valid XML, it needs a ROOT element. I also need to tell it to be XML, using the TYPE directive.
But this lets me hook into the value of /MyString[1], and return that as varchar(max).
And it works, my data comes back as , , etc.
It’s a habit I need to use more often.

Edit: I can also skip the ROOT element (but keep TYPE) and use .value('(./text())[1]','varchar(max)') - but for some reason it's always felt strange to me to be returning something which I declare to be XML (using TYPE) without it being well-formed.

Source : http://sqlblog.com/blogs/rob_farley/archive/2010/04/15/handling-special-characters-with-for-xml-path.aspx

Find Relationship of Foreign Key and Primary Key using T-SQL

SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
 OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
 SCHEMA_NAME(o.SCHEMA_ID) as ReferenceSchemaName,

OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
ON f.OBJECT_ID = fc.constraint_object_id

where OBJECT_NAME(f.parent_object_id) = 'TableName'

Date Name

 -- Each datepart and its abbreviations return the same value.

-- Year Name

     

      -- Datepart

            SELECT DATENAME(YEAR,GETDATE())

     

      -- Abbreviations

            SELECT DATENAME(YYYY,GETDATE())

            SELECT DATENAME(YY,GETDATE())

-- Day of Year

     

      -- Datepart

            SELECT DATENAME(DAYOFYEAR,GETDATE())

     

      -- Abbreviations

            SELECT DATENAME(y,GETDATE())

            SELECT DATENAME(DY,GETDATE())

-- Quarter

      -- Datepart

            SELECT DATENAME(QUARTER,GETDATE())

           

      -- Abbreviations

            SELECT DATENAME(Q,GETDATE())

            SELECT DATENAME(QQ,GETDATE())

-- Month Name

      -- Datepart

            SELECT DATENAME(MONTH,GETDATE())

     

      -- Abbreviations

            SELECT DATENAME(M,GETDATE())

            SELECT DATENAME(MM,GETDATE())

-- Week

            -- Week Day Name

           

                        -- Datepart

                        SELECT DATENAME(WEEKDAY,GETDATE())

                       

                        -- Abbreviations

                        SELECT DATENAME(DW,GETDATE())

                        SELECT DATENAME(W,GETDATE())

           

            -- Week Number in year

           

                        -- Date Part

                        SELECT DATENAME(WEEK,GETDATE())

                       

                        -- Abbreviations

                        SELECT DATENAME(WK,GETDATE())

            -- Week Number in year as per ISO

           

                        -- Date Part

                        SELECT DATENAME(ISO_WEEK,GETDATE())

                       

                        -- Abbreviations

                        SELECT DATENAME(ISOWK,GETDATE())

                        SELECT DATENAME(ISOWW,GETDATE())

           

-- Day Number of Month

     

      -- Datepart

            SELECT DATENAME(DAY,GETDATE())

           

      -- Abbreviations

            SELECT DATENAME(DD,GETDATE())

           

-- Hour

     

      -- Datepart

      SELECT DATENAME(HOUR,GETDATE())

     

      -- Abbreviations

      SELECT DATENAME(HH,GETDATE())

-- Minute

      -- Datepart

            SELECT DATENAME(MINUTE,GETDATE())

     

      -- Abbreviations

            SELECT DATENAME(MI,GETDATE())

            SELECT DATENAME(N,GETDATE())

-- Seconds

     

      -- Datepart

            SELECT DATENAME(SECOND,GETDATE())

           

      -- Abbreviations

            SELECT DATENAME(S,GETDATE())

            SELECT DATENAME(SS,GETDATE())

-- Milli seconds

     

      -- Datepart

            SELECT DATENAME(MILLISECOND,GETDATE())

           

      -- Abbreviations

            SELECT DATENAME(MS,GETDATE())

-- Microseconds

      -- Datepart

            SELECT DATENAME(MICROSECOND,GETDATE())

           

      -- Abbreviations

            SELECT DATENAME(MCS,GETDATE())

-- NanoSeconds

     

      -- Datepart

            SELECT DATENAME(NANOSECOND,GETDATE())

           

      -- Abbreviations

            SELECT DATENAME(NS,GETDATE())