Useful Sql

Here are some scripts that I have found online and kept back for resuse or fine tuned them for specific tasks. Credit and thanks go to the original authors.


Query to list all files in a directory
/*List all files in a directory*/
DECLARE  @PathName       VARCHAR(256) ,
         @CMD            VARCHAR(512)
 
CREATE TABLE #CommandShell ( Line VARCHAR(max))
 
SET @PathName = '\\SERVER\FOLDER'
SET @CMD = 'DIR ' + @PathName + ' /B'
 
-- Insert from stored procedure execution
INSERT INTO #CommandShell EXEC MASTER..xp_cmdshell   @CMD
 
SELECT * FROM #COMMANDSHELL ORDER BY 1
DROP TABLE #COMMANDSHELL
Query to display the SSRS execution log
/*SSRS Report Execution Log*/ 
SELECT
    TimeStart,
    Catalog.Type,
    Catalog.Name,
    TimeDataRetrieval,
    TimeProcessing,
    TimeRendering,
    ByteCount,
    [RowCount]
FROM
    Catalog
INNER JOIN ExecutionLog
    ON Catalog.ItemID = ExecutionLog.ReportID
    --and Catalog.Name = ''
WHERE
    Type = 2
ORDER BY 1
Query to Find Columns whose name contains specific Text
/*Search Column Names*/
SELECT
    t.name AS table_name
   ,schema_name(schema_id) AS schema_name
   ,c.name AS column_name
FROM
    sys.tables AS t
INNER JOIN sys.columns c
    ON t.object_id = c.object_id
WHERE
    c.name LIKE '%Search Text Goes Here%'
ORDER BY
    schema_name
   ,table_name;
Query to Find Tables whose name contains specific Text
/*Search Table Names*/
SELECT
    t.name AS table_name
   ,schema_name(schema_id) AS schema_name
FROM
    sys.tables AS t
WHERE
    t.name LIKE '%Search Text Goes Here%';
Query to Find SPs containing specific Text
/*Search Stored Procedures*/
SELECT
    o.name
FROM
    sysobjects o
INNER JOIN syscomments c
    ON c.id = o.id
WHERE
    o.type = 'P'
    AND c.text LIKE '%Search Text Goes Here%'
ORDER BY
    o.name ASC