SQL Server Tip: Dynamic SQL Dummy References

Programming, SQL Server / By /
dynamic sql code


Dynamic SQL Can Hide Dependencies

An often overlooked aspect of programming is creating code that is supportable and in SQL an important aspect supportable code is object dependencies. Usually you don’t need to think much about these when coding in SQL Server, but a unique situation can arise when using dynamic SQL.

Consider that you want to deprecate a table in your database and update its dependent objects (objects that use the table) to use a new object. An obvious analysis task would be to check all of the objects that depend on the table to be deprecated.

But what if the table is obfuscated in a dynamic SQL block? See the SQL examples below.

-- if used in a proc, this would produce an object refence
select max(Id)
from dbo.TestDependencies

-- if used in a proc, this would NOT produce an object reference
declare @SQL_BLOCK varchar(max) = 
	'select max(Id)
	from dbo.TestDependencies'
exec(@SQL_BLOCK)

If the only instance of an object within a stored procedure, trigger, etc. is as part of a text string, then generally the system will keep no record of that dependency. This can make debugging and developing on the system harder than it should be. Other methods such as scanning object command text can still reveal the dependency, but they are more cumbersome than a simple complete list of dependencies.


Use Dummy References to Create a Dependency Record

Use the code below to create an example table, some data, and a two dependent objects (a view and a stored procedure).

-- create table and data

drop table if exists dbo.TestDependency;
create table dbo.TestDependency (
	Id int identity(1,1) not NULL
	,IsActive bit not NULL
);
insert into dbo.TestDependency (IsActive)
values
	(1),(1),(0),(1),(0)
;


-- create view that depends on table

drop view if exists dbo.vw_TestDependency;
go
create view dbo.vw_TestDependency
as
select * from dbo.TestDependency;
go


-- create procedure that depends on table

drop proc if exists dbo.sp_TestDependency1;
go
create proc dbo.sp_TestDependency1
as
select * from dbo.TestDependency;
go

Now create a final procedure, but also notice that the table in this example is hidden within the dynamic SQL text block. Because the only reference to the table in this procedure is in a text string, SQL Server will NOT create an object dependency record for it. Additionally, if you look in the catch section you will also notice that there is a commented out declare variable statement. If uncommented this statement will create a dependency record within the database and adds practically zero processing time to the procedure.

drop proc if exists dbo.sp_TestDependency2;
go
create proc dbo.sp_TestDependency2
	@IsActive bit = 1
	,@Debug bit = 0
as
begin try

	declare @SQL_Block nvarchar(max) =

		/*-----------------------------------------
		|   TABLE REFERENCE INSIDE THIS BLOCK!!
		|
		|   For complex dynamic SQL it is not a bad 
		|   idea to ensure it prints out neatly
		|   formatted.
		-----------------------------------------*/
		'select *' + char(13) + char(10)
		+ 'from dbo.TestDependency' + char(13) + char(10)
		+ 'where IsActive = isnull(@IsActive,IsActive)'


	if @Debug = 1
		/*----------------------------------------------
		|   Raiserror with -1 does not trigger an error.
		|
		|   Additionally, its no wait option can be used
		|   to print text in realtime instead of waiting
		|   for the proc to complete.
		----------------------------------------------*/
		raiserror(@SQL_Block,-1,-1) with nowait

	exec sys.sp_executesql
		@stmt = @SQL_Block
		,@params = N'@IsActive bit'
		,@IsActive = @IsActive

end try
begin catch
	
	/*---------------------------------------------------------
	|   There are many ways to ensure the reference essentially 
	|   doesn't do anything. An if statement with criteria that 
	|   is always false, where clause with same, selecting 
	|   top(1) NULL, etc.
	|
	|   Any one of these methods would be fine.
	---------------------------------------------------------*/
	/*if 1 = 0
		declare @DummyRef bit = (
			select top(1) NULL
			from dbo.TestDependency 
			where 1 = 0
		)*/

end catch
go

After creating the procedure you can test the effect of this dummy reference. Right click on the table within the SSMS object explorer and you can view the dependencies.

Perform this action when the dummy reference is commented out and when it is not and you will see that the second procedure drops off of the list of dependencies.

how to 'view dependencies' in SSMS

It is difficult to see with the small text, but there there are two dependencies on the left and on the right there are three.

dependencies WITHOUT dummy reference to obfuscated table
Without Dummy Reference
dependencies WITH dummy reference to obfuscated table
With Dummy Reference

You can also test the dependencies by querying a system table rather than using the SSMS GUI.

select *
from sys.sql_expression_dependencies
where referenced_id = object_id('dbo.TestDependency')

Related Posts

267 thoughts on “SQL Server Tip: Dynamic SQL Dummy References”

  21. MichaelCat

    Welcome to our portal, your premier source for all the newest news and updates on the broadcasting landscape in the United Kingdom. Whether you’re interested in broadcasting, FM/AM, publishing, or web-based media, we deliver thorough coverage that keeps you updated about the key developments and patterns. From just-in bulletins to comprehensive analyses, our team of experienced journalists and industry specialists work relentlessly to bring you the most precise and up-to-date news – https://ukeventnews.uk/apple-music-isn-t-playing-tracks-playback-issue/
    In alongside to updates, we offer informative features and opinion essays that delve into the details of the communications industry. Our features cover a diverse array of topics, including regulatory shifts, media control, and the impact of new innovations. We also highlight the milestones and difficulties faced by media professionals, providing a platform for voices from across the industry to be noticed and acknowledged.
    Stay in touch with the pulse of the UK media scene through our regularly updated content. Whether you’re a media professional, a student, or simply a media enthusiast, our site is designed to serve to your needs and needs. Enter our growing community of readers and make sure you’re always in the know about the dynamic and constantly changing world of media in the United Kingdom.

  24. Robertvok

    Водоподготовка осуществляет значимую роль в гарантировании эффективной работы производственного оборудования – https://machinetechsolutions.ru/dungs-vdk-opisanie-i-primenenie-2/. Метод включает в себя фильтрацию и настройку воды для нейтрализации загрязнений, таких как растворимые соли, органические соединения и вредные бактерии. Это необходимо для избежания разрушения, накипи и иных проблем, которые могут ухудшить эффективность техники и сократить период эксплуатации. Внедрение правильной водоподготовки позволяет не только улучшить надёжность и долговечность оборудования, но и минимизировать расходы на эксплуатацию и обслуживание.

    Текущие системы водоподготовки содержат разнообразие технологических процессов и техники. Среди них особо выделяются механические очистители, предназначенные для удаления больших частиц, системы ультрафильтрации, которые эффективно устраняют солевые элементы, и УФ-установки, дезинфицирующие воду. Также важно отметить реагенты, применяемые для корректировки pH и предотвращения ржавчины. Внедрение автоматизированных систем управления даёт возможность значительно улучшить результативность и точность процесса водоподготовки, что особенно важно в условиях масштабного промышленного производства.

    Эффективная водоподготовка положительно влияет на состояние экосистемы, уменьшая количество выбросов вредных веществ в атмосферу. Внедрение современных технологий и устройств снижает потребление воды и её загрязнение веществами, что соотносится с требованиями устойчивого развития. Производственные компании, занимающиеся водоподготовкой, не только улучшают эффективность, но и проявляют осознанность к окружающей среде. В результате, качественная водоподготовка становится важным конкурентным преимуществом и вложением в устойчивое развитие, как для производств, так и для социума.

  30. ラブドール

    What’s most placing about the company, however, エロ 人形is usually that it’s the sole dependable distributor we’ve found which has an genuine return plan. Usually, return procedures from doll distributors is often summed up in three words and phrases: All revenue closing.

  35. オナホ 手作り

    ensuring that it feels genuine and responsive to their needs.エロ 人形Actionable Strategies for Alleviating LonelinessOffering support to someone with cancer goes beyond open-ended offers of help; it requires specific,

  39. ラブドール

    and I am excited to see how your insights will continue to shape and enhance my understanding and approach to [specific topic].ダッチワイフand I am grateful for the depth and practical value you bring to your writing.

  63. エロ ラブドール

    彼女は完全な胸、腹部、太ももを持っており、ラブドール エロ余分な脂肪はなく、モデルと同じように見えます。同時に、彼女は性的スキルを行使するための非常に優れたツールでもあり、あなたのガールフレンドのように文句を言うことはありません。

  79. リアルドール

    中国 エロfrom the skin tone and facial features to the hair color and body type.This level of customization ensures that your doll is not just a product but a unique creation tailored to your specific preferences.

  95. ダッチワイフ

    ラブドール エロAn adult child’s decision to limit or end contact with an abusive parent is nearly always a slow and agonizing one that becomes a matter of safety and survival.It is human to yearn for love from our parents and blame ourselves for not getting it.

  98. ラブドールエロ

    Here is the model that invented the item up to now, dolls of this manufacturer go from 4500 euros.ドール エロ In recent times, several makers have began making these merchandise. Because the earnings design of these suppliers is different, they provide them at A great deal decrease costs.

  106. エロ 人形

    ラブドール エロThis is why narcissists react with rage overt or passive-aggressive when others do not reflect back what they wish to believe about themselves.A dimension of narcissistic grandiosity is narcissists’ belief that they never have enough of what they deserve.

  107. ラブドール えろ

    But beyond that,えろ 人形 Val Curtis of the London School of Hygiene and Tropical Medicine (described in a 2012 New York Times article as a “disgustologist”) believes that disgust is the guiding force for much of our social life as well.

  114. リアルラブドール

    That is, until it’s challenged. ‘We often don’t give men permission to talk about the things that lead to low sex drive, such as relationship dynamics, stress and the daily grind of life,

  131. ラブドール 高級

    Contrary to popular belief, there is a robust marketplace for counterfeit sexual エロ 人形intercourse dolls. The businesses on our checklist are authorized vendors for important doll makers, most of that happen to be based in China.

  147. sootoca

    Ultrastructure of thyroid C cells in sham operated SO, orchidectomized Orx and Orx rats treated with tamoxifen Orx TAM priligy for pe Fibrosis of the bladder and lower ureters with hydroureter and hydronephrosis occurs in the lower third of the ureter or in the bladder

  164. 人形 エロ

    ラブドールの件に関してはむしろ増える可能性すら考えられるし、セックス ロボットそもそもラブドールはドール愛好家に購入されることもある位、その価値はとても広い意味を持ってると思う。

  166. オナホ

    That may be why our web-site provides low cost life-very えろ 人形long great love dolls intended to offer you fantasy sex expert services and also to function a companion in your lifetime.

  182. フィギュア エロ

    Your innovative perspective and comprehensive analysis have inspired me to think more deeply and creatively about [specific area],and I am confident that your future articles will continue to offer valuable insights and practical advice that are both enlightening and transformative.ダッチワイフ

  183. エロ

    is that a good moment to be having sex?”No matter how much you’re trying to pump the accelerator,エロ 人形chronic stress can completely squelch your sex drive,

  185. えろ

    Try Different PositionsWe’re all for checking off every position from the latest Cosmopolitan guide.ラブドール えろBut let’s make one thing clear: Trying new sex positions doesn’t mean being an acrobat.

  192. ラブドール オナホ

    “Mindfulness practice provides direct practice in turning their attention away from their thoughts and onto their physical experience instead … Lack of pleasure usually indicates some sort of physical or mental block.” .. Lack of pleasure usually indicates some sort of physical or mental block.”Aaron encourages people to turn the practice of mindfulness into a daily habit and to try to set aside at least 10 minutes a day to meditate,リアル ドール

  194. えろ

    ラブドール えろAs shown in Figure 1,sexually active adolescents who do not consistently use contraceptives will usually become pregnant and have to face potentially life altering decisions about resolving their pregnancy through abortion,

  211. えろ

    Hormonal changes around the time of menopause can cause vaginal dryness that makes for painful A water-based lubricant is safest to use with condoms.人形 エロBut,

  227. えろ

    jpはあなたの求めるダッチワイフ公式正規通販ショップの目的地であり、ラブドール エロ等身大ドールを購入するのに最適な場所です ?その理由は次のとおりです。

  232. ラブドール 男

    セックス ドールHey would you mind letting me know which hosting company you’re working with?I’ve loaded your blog in 3 completely different web browsers and I must say this blog loads a lot faster then most.Can you suggest a good hosting provider at a fair price?Thanks,

  252. オナドール

    Almost all mixtures are feasible, if you favor a different overall body variety together with your lover, be sure to Speak to us. Each deal with could be coupled with each and every system sort.

Leave a Comment

Your email address will not be published. Required fields are marked *