Why Does Microsoft SQL Server Check Columns But Not Tables in Stored Procs

Why does Microsoft SQL Server check columns but not tables in stored procs?

This is called deferred name resolution.

There is no way of turning it off. You can use dynamic SQL or (a nasty hack!) add a reference to a non existent table so that compilation of that statement is deferred.

CREATE PROCEDURE [dbo].[MyProcedure]
AS
BEGIN

CREATE TABLE #Dummy (c int)

    SELECT
        NonExistantCol1, NonExistantCol2, NonExistantCol3
    FROM
        ExistantTable 
    WHERE NOT EXISTS(SELECT * FROM #Dummy)    

DROP TABLE #Dummy

END
GO

How can I get a list of all columns referenced in a stored procedure?

When a stored procedure is executed it is parsed and compiled into a query plan, this is cached and you can access it via sys.dm_exec_cached_plans and sys.dm_exec_query_plan in XML format. The query plan records the 'output list' of each section of the parsed code. Seeing which columns are used by the stored procedure is just a matter of querying this XML, like this:

--Execute the stored procedure to put its query plan in the cache
exec sys.sp_columns ''

DECLARE @TargetObject nvarchar(100) = 'sys.sp_columns';

WITH XMLNAMESPACES (
    'http://schemas.microsoft.com/sqlserver/2004/07/showplan' as ns1
), CompiledPlan AS (
    SELECT 
        (SELECT query_plan FROM sys.dm_exec_query_plan(cp.plan_handle)) qp,
        (SELECT ObjectID FROM sys.dm_exec_sql_text(cp.plan_handle)) ob
    FROM sys.dm_exec_cached_plans cp
    WHERE objtype = 'Proc'
), ColumnReferences AS (
    SELECT DISTINCT
        ob,
        p.query('.').value('./ns1:ColumnReference[1]/@Database', 'sysname') AS [Database],
        p.query('.').value('./ns1:ColumnReference[1]/@Schema', 'sysname') AS [Schema],
        p.query('.').value('./ns1:ColumnReference[1]/@Table', 'sysname') AS [Table],
        p.query('.').value('./ns1:ColumnReference[1]/@Column', 'sysname') AS [Column]
    FROM CompiledPlan
        CROSS APPLY qp.nodes('//ns1:ColumnReference') t(p)
)

SELECT 
    [Database], 
    [Schema], 
    [Table], 
    [Column]
FROM ColumnReferences 
WHERE 
    [Database] IS NOT NULL AND 
    ob = OBJECT_ID(@TargetObject, 'P')

Caveat emptor this depends on how you define 'used'. It may be that a CTE within your stored procedure references 5 columns from a table, but then when this CTE is used only three of the columns are passed on. The query optimizer may ignore these extra fields and not include them in the plan. On the flip side the optimizer may decide that it can make a more efficient query by including extra fields in an output to enable it to use a better index later on. This code will return the columns used by the query plan, they may not exactly be the columns that are in the stored procedure code.

How to find out which stored procedure is using the specific column of a specific table in SQL Server?

You don't need to use sys.sql_dependencies which has been deprecated, you can use the newer sys.dm_sql_referenced_entities instead.

Unfortunately, after some testing, it turns out that sys.sql_expression_dependencies and sys.dm_sql_referencing_entities do not return information on column-level dependencies.

SELECT OBJECT_SCHEMA_NAME(o.object_id),
       OBJECT_NAME(o.object_id)
FROM sys.objects o
JOIN sys.schemas s ON s.schema_id = o.schema_id
CROSS APPLY sys.dm_sql_referenced_entities(QUOTENAME(s.name) + '.' + QUOTENAME(o.name), 'OBJECT') d
INNER JOIN sys.columns c
    ON c.object_id = d.referenced_id
    AND c.column_id = d.referenced_minor_id
    AND c.object_id = OBJECT_ID('dbo.Customers')
    AND c.name = 'FullName'
WHERE o.type IN ('FN','TF','IF','P','V','TR');  -- scalar, multi-line and inline funcs, procs, views, triggers

Find all stored procedures that reference a specific column in some table

One option is to create a script file.

Right click on the database -> Tasks -> Generate Scripts

Then you can select all the stored procedures and generate the script with all the sps. So you can find the reference from there.

Or

-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'CreatedDate' + '%'
GO

-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'CreatedDate' + '%'
GO

Source SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Find the stored procedure which uses a particular table in sql server

The following query maybe of help to you.

SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS S
INNER JOIN SYS.OBJECTS O ON O.Object_Id = S.id
WHERE S.TEXT LIKE '%Table_name%'
    AND O.type = 'P'

It can search if a particular word is contained in the stored procedure. If a table name is what you need to find, then it can find it from the stored procedure text. Hope it helps.

---

Related Topics