SQL Server - Get Table Dependencies via INFORMATION_SCHEMA - Template for SSMS

The following is a SQL Server script used to get a table's dependencies. The script uses SSMS template syntax, so you can add it to the SSMS Template Explorer and use the (Ctrl-Shift-M) keyboard sequence to set the script's parameters. The script returns the following for each dependency:

• Parent Table Schema Name
• Parent Table Name
• Parent Table Primary Key Field Name
• Foreign Table Schema Name
• Foreign Table Name
• Foreign Table Foreign Key Field Name
• Foreign Table Foreign Key Constraint Name

What's nice is that the script uses INFORMATION_SCHEMA, so it may be used with other databases that support INFORMATION_SCHEMA; however, I have not tested the script with other databases. If you give the script a run on another database, I'd love to here about your experience.

-- ======================================================================
-- Template used to determine a specific table's dependencies.
--
-- Use the Specify Values for Template Parameters command (Ctrl-Shift-M) 
-- to fill in the parameter values below.
-- @schema_name:  the schema name of the table to retrieve dependencies
-- @table_name:  the table name of the table to retrieve dependencies
-- ======================================================================

DECLARE @SchemaName VARCHAR(128), @TableName VARCHAR(128);
SELECT @SchemaName = N'<schema_name, VARCHAR(128), dbo>', @TableName = N'<table_name, VARCHAR(128),>'

SELECT DISTINCT  
       pt.TABLE_SCHEMA AS ParentSchema, 
       pt.TABLE_NAME AS ParentTable, 
       pt.COLUMN_NAME AS ParentPrimaryField, 
       fk.TABLE_SCHEMA AS ForeignSchema, 
       fk.TABLE_NAME AS ForeignTable, 
       cu.COLUMN_NAME AS ForeignKeyField, 
       c.CONSTRAINT_NAME AS ForeignKeyConstraint 
FROM   INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c 
       INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk 
            ON  c.CONSTRAINT_NAME = fk.CONSTRAINT_NAME 
       INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk 
            ON  c.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
       INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu 
            ON  c.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
       INNER JOIN ( 
                SELECT tc.TABLE_SCHEMA, 
                       tc.TABLE_NAME, 
                       kcu.COLUMN_NAME 
                FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
                       INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
                            ON  tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME 
                WHERE  (tc.CONSTRAINT_TYPE = 'PRIMARY KEY') 
            ) pt 
            ON  pt.TABLE_NAME = pk.TABLE_NAME 
WHERE  (pk.TABLE_NAME = @TableName) 
       AND (pk.TABLE_SCHEMA = @SchemaName) 
ORDER BY 
       ForeignTable ASC; 

Thanks for reading...

UPDATE STATISTICS for all database tables

/***** BEGIN - UPDATE STATISTICS *****/
DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), TableName VARCHAR(128))

INSERT INTO @t(SchemaName, TableName)
SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES t
WHERE t.table_type = 'BASE TABLE'

DECLARE @Rows INT, @i INT;

SELECT @Rows = (SELECT COUNT(RowID) FROM @t), @i = 1;

WHILE(@i <= @Rows)
BEGIN
 DECLARE @sqlCommand NVARCHAR(512);

 SELECT @sqlCommand =
     'UPDATE STATISTICS ' +
     t.SchemaName + '.' +
     t.TableName +
     ' WITH FULLSCAN'
 FROM @t t
 WHERE t.RowID = @i;

 PRINT @sqlCommand
 EXEC sp_executesql @sqlCommand;

 SET @i = @i + 1;
END
GO
/***** END - UPDATE STATISTICS *****/

SSRS - SQL Server Reporting Server - External Image Issue (and Solution)

The other day I encountered some complicated issues with SQL Server Reporting Server (SSRS) and including external images from a file store that was not directly accessible to users (or SSRS).  I've always assumed that the credentials that you pass to SSRS to generate reports where the contextual credentials in which the reports were generated (aka impersonation).  Well, that assumption FAILED to materialize.

The intent of this post is to hopefully assist others with similar issues and to document my findings for future use and reference.

So, here the scenario and configuration (all in a TEST environment which parallels the PRODUCTION environment):

• Box 1: Web Server
• IIS 6
• Web App: MyWebApp
• File Store Virtual Directory: MyWebAppFileStoreVd
• Box 2: SQL Server
• Box 3: SSRS
• Box 4: File Store
• MyWebApp File Store: MyWebAppFileStore

As a developer, I had access to the file store (Windows permissions).  So, while creating reports in Visual Studio's Report Designer with hard coded image paths (for testing, because dynamic wasn't working - this is the issue), I could view the images in the report.  However, once the reports were deploy to SSRS, the image placeholders contained no path information and the infamous 'red X.'  This was also the case when the report was request and render via the Web app.

Ahhhh!  What's the problem?  The file store contained permissions for the SSRS user with the credentials passed to SSRS from the Web app.  After a couple of day of 'banging head on desk, computer, floor, etc...), I had an epiphany - maybe the SSRS user is not impersonated???  That was it - the user is NOT impersonated!  Reports are actually generated via the service account of SSRS - this makes perfect sense now; however, I had always assumed that impersonation was taking place.  I digress...

I've read that Microsoft's solution is to run the SSRS service as a domain account and give that domain account whatever permissions it needs on the file store.  Well, that is NOT a solution in our environment.  So, what did we do to solve the issue?  Well, we...

We decided to create a virtual directory on the Web Server (Box 1) that points to the aforementioned File Store (Box 4) and restricts access (over http) to a specific computer via IP - the SSRS IP (Box 3).  This essentially creates a File Store (Box 4) web share proxy on the Web Server (Box 1) accessible only to SSRS (Box 3) over http.

The following is the process and associated steps taken to successfully enable this scenario:

1. Create a Virtual Directory outside of the MyWebApp Virtual Directory that points to the MyWebAppFileStore File Store - /MyWebAppFileStoreVd/
2. Set MyWebAppFileStoreVd Virtual Directory’s Directory Security to ‘Enable Anonymous Access’ and clear all ‘Authenticated Access’ checkboxes.
1. Virtual Directory Properties -- Directory Security Tab - Edit button (red highlighted button).
   
   
   
2. Check the ‘Enable Anonymous Access’ checkbox.
3. Clear ‘Authenticated Access’ checkboxes.
4. Click ‘Ok’ and then ‘Apply.’
   
   
   
3. Restrict access by the Report Server’s IP.
1. Virtual Directory Properties -- Directory Security Tab -- Edit button (green highlighted button).
   
   
   
2. Select the ‘Denied Access’ radio button within the ‘IP Address Access Restrictions’ group box.
3. Click the ‘Add…’ button.
   
   
   
4. Select the ‘Single Computer’ radio button in the ‘Grant Access’ dialog box.
5. Enter the Report Server’s IP address in the IP Address masked textbox.
   
   
   
6. Click ‘Ok’, ‘Ok’, ‘Apply’, and ‘Ok.’

That should about do it...

While this implementation works and works well for my specific scenario, this may not be the best implementation for your specific scenario.  I hope that this post saves at least one person the hours of troubleshooting and headaches that I incurred getting the implementation to work...

Thanks for reading...

Drop All Schemas in a SQL Server Database

This post extends my previous post Drop All Objects in a SQL Server Database and drops all non-system related Schemas in a database.

/***** BEGIN - DROP SCHEMAs *****/
DECLARE @name VARCHAR(128), @sqlCommand NVARCHAR(1000), @Rows INT = 0, @i INT = 1;
DECLARE @t TABLE(RowID INT IDENTITY(1,1), ObjectName VARCHAR(128));

INSERT INTO @t(ObjectName)
SELECT s.[SCHEMA_NAME] FROM INFORMATION_SCHEMA.SCHEMATA s
WHERE s.[SCHEMA_NAME] NOT IN('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'db_owner', 'db_accessadmin', 'db_securityadmin', 'db_ddladmin', 'db_backupoperator', 'db_datareader', 'db_datawriter', 'db_denydatareader', 'db_denydatawriter')

SELECT @Rows = (SELECT COUNT(RowID) FROM @t), @i = 1;

WHILE (@i <= @Rows) 
BEGIN
    SELECT @sqlCommand = 'DROP SCHEMA [' + t.ObjectName + '];', @name = t.ObjectName FROM @t t WHERE RowID = @i;
    EXEC sp_executesql @sqlCommand;        
    PRINT 'Dropped SCHEMA: [' + @name + ']';    
    SET @i = @i + 1;
END
GO
/***** END - DROP SCHEMAs *****/

Drop All Objects in a SQL Server Database

The following SQL script is similar to Paige Cook's Drop All Objects in a SQL Server Database; however, is if fully INFORMATION_SCHEMA based and supports Schema-based objects (e.g. SchemaName.TableName).

/***** BEGIN - DROP non-sys PROCs *****/
DECLARE @schema VARCHAR(128), @name VARCHAR(128), @sqlCommand NVARCHAR(1000), @Rows INT = 0, @i INT = 1;
DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128));

INSERT INTO @t(SchemaName, ObjectName)
SELECT r.ROUTINE_SCHEMA, r.SPECIFIC_NAME
FROM INFORMATION_SCHEMA.ROUTINES r
WHERE r.ROUTINE_TYPE = 'PROCEDURE'
ORDER BY r.ROUTINE_SCHEMA, r.ROUTINE_NAME

SELECT @Rows = (SELECT COUNT(RowID) FROM @t), @i = 1;

WHILE (@i <= @Rows) 
BEGIN
    SELECT @sqlCommand = 'DROP PROC [' + t.SchemaName + '].[' + t.ObjectName + '];', @schema = t.SchemaName, @name = t.ObjectName FROM @t t WHERE RowID = @i;
    EXEC sp_executesql @sqlCommand;        
    PRINT 'Dropped PROC: [' + @schema + '].[' + @name + ']';    
    SET @i = @i + 1;
END
GO
/***** END - DROP non-sys PROCs *****/

/***** BEGIN - DROP FUNCTIONs *****/
DECLARE @schema VARCHAR(128), @name VARCHAR(128), @sqlCommand NVARCHAR(1000), @Rows INT = 0, @i INT = 1;
DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128));

INSERT INTO @t(SchemaName, ObjectName)
SELECT r.ROUTINE_SCHEMA, r.SPECIFIC_NAME
FROM INFORMATION_SCHEMA.ROUTINES r
WHERE r.ROUTINE_TYPE = 'FUNCTION'
ORDER BY r.ROUTINE_SCHEMA, r.ROUTINE_NAME

SELECT @Rows = (SELECT COUNT(RowID) FROM @t), @i = 1;

WHILE (@i <= @Rows) 
BEGIN
    SELECT @sqlCommand = 'DROP FUNCTION [' + t.SchemaName + '].[' + t.ObjectName + '];', @schema = t.SchemaName, @name = t.ObjectName FROM @t t WHERE RowID = @i;
    EXEC sp_executesql @sqlCommand;        
    PRINT 'Dropped FUNCTION: [' + @schema + '].[' + @name + ']';    
    SET @i = @i + 1;
END
GO
/***** END - DROP FUNCTIONs *****/

/***** BEGIN - DROP VIEWs *****/
DECLARE @schema VARCHAR(128), @name VARCHAR(128), @sqlCommand NVARCHAR(1000), @Rows INT = 0, @i INT = 1;
DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128));

INSERT INTO @t(SchemaName, ObjectName)
SELECT t.TABLE_SCHEMA, t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE = 'VIEW'
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME

SELECT @Rows = (SELECT COUNT(RowID) FROM @t), @i = 1;

WHILE (@i <= @Rows) 
BEGIN
    SELECT @sqlCommand = 'DROP VIEW [' + t.SchemaName + '].[' + t.ObjectName + '];', @schema = t.SchemaName, @name = t.ObjectName FROM @t t WHERE RowID = @i;
    EXEC sp_executesql @sqlCommand;        
    PRINT 'Dropped VIEW: [' + @schema + '].[' + @name + ']';    
    SET @i = @i + 1;
END
GO
/***** END - DROP VIEWs *****/

/***** BEGIN - DROP FOREIGN KEY CONSTRAINTs *****/
DECLARE @schema VARCHAR(128), @name VARCHAR(128), @tableSchema VARCHAR(128), @tableName VARCHAR(128), @sqlCommand NVARCHAR(1000), @Rows INT = 0, @i INT = 1;
DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128), TableSchema VARCHAR(128), TableName VARCHAR(128));

INSERT INTO @t(SchemaName, ObjectName, TableSchema, TableName)
SELECT tc.CONSTRAINT_SCHEMA, tc.CONSTRAINT_NAME, tc.TABLE_SCHEMA, tc.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE (tc.CONSTRAINT_CATALOG = DB_NAME()) AND (tc.CONSTRAINT_TYPE = 'FOREIGN KEY')
ORDER BY tc.TABLE_SCHEMA, tc.TABLE_NAME, tc.CONSTRAINT_SCHEMA, tc.CONSTRAINT_NAME

SELECT @Rows = (SELECT COUNT(RowID) FROM @t), @i = 1;

WHILE (@i <= @Rows) 
BEGIN
    SELECT @sqlCommand = 'ALTER TABLE [' + t.TableSchema + '].[' + t.TableName + '] DROP CONSTRAINT [' + t.ObjectName + '];', 
        @schema = t.SchemaName, @name = t.ObjectName, @tableSchema = t.TableSchema, @tableName = t.TableName
    FROM @t t WHERE RowID = @i;
    EXEC sp_executesql @sqlCommand;        
    PRINT 'Dropped FOREIGN KEY CONSTRAINT: ' + @name + ' on [' + @tableSchema + '].[' + @tableName + ']';
    SET @i = @i + 1;
END
GO
/***** END - DROP FOREIGN KEY CONSTRAINTs *****/

/***** BEGIN - DROP PRIMARY KEY CONSTRAINTs *****/
DECLARE @schema VARCHAR(128), @name VARCHAR(128), @tableSchema VARCHAR(128), @tableName VARCHAR(128), @sqlCommand NVARCHAR(1000), @Rows INT = 0, @i INT = 1;
DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128), TableSchema VARCHAR(128), TableName VARCHAR(128));

INSERT INTO @t(SchemaName, ObjectName, TableSchema, TableName)
SELECT tc.CONSTRAINT_SCHEMA, tc.CONSTRAINT_NAME, tc.TABLE_SCHEMA, tc.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE (tc.CONSTRAINT_CATALOG = DB_NAME()) AND (tc.CONSTRAINT_TYPE = 'PRIMARY KEY')
ORDER BY tc.TABLE_SCHEMA, tc.TABLE_NAME, tc.CONSTRAINT_SCHEMA, tc.CONSTRAINT_NAME

SELECT @Rows = (SELECT COUNT(RowID) FROM @t), @i = 1;

WHILE (@i <= @Rows) 
BEGIN
    SELECT @sqlCommand = 'ALTER TABLE [' + t.TableSchema + '].[' + t.TableName + '] DROP CONSTRAINT [' + t.ObjectName + '];', 
        @schema = t.SchemaName, @name = t.ObjectName, @tableSchema = t.TableSchema, @tableName = t.TableName
    FROM @t t WHERE RowID = @i;
    EXEC sp_executesql @sqlCommand;        
    PRINT 'Dropped PRIMARY KEY CONSTRAINT: ' + @name + ' on [' + @tableSchema + '].[' + @tableName + ']';
    SET @i = @i + 1;
END
GO
/***** END - DROP PRIMARY KEY CONSTRAINTs *****/

/***** BEGIN - DROP TABLEs *****/
DECLARE @schema VARCHAR(128), @name VARCHAR(128), @sqlCommand NVARCHAR(1000), @Rows INT = 0, @i INT = 1;
DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128));

INSERT INTO @t(SchemaName, ObjectName)
SELECT t.TABLE_SCHEMA, t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME

SELECT @Rows = (SELECT COUNT(RowID) FROM @t), @i = 1;

WHILE (@i <= @Rows) 
BEGIN
    SELECT @sqlCommand = 'DROP TABLE [' + t.SchemaName + '].[' + t.ObjectName + '];', @schema = t.SchemaName, @name = t.ObjectName FROM @t t WHERE RowID = @i;
    EXEC sp_executesql @sqlCommand;        
    PRINT 'Dropped TABLE: [' + @schema + '].[' + @name + ']';    
    SET @i = @i + 1;
END
GO
/***** END - DROP TABLEs *****/