SQL Server Tip: Dynamic SQL Dummy References

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')

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

  1. 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.

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

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

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

  3. 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.

  4. 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,

  5. 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.

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

  7. 中国 エロ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.

  8. ラブドール エロ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.

  9. 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.

  10. ラブドール エロ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.

  11. 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.

  12. 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,

  13. 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.

  14. 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

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

  16. 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.

  17. 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.ダッチワイフ

  18. 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,

  19. 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.

  20. “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,リアル ドール

  21. ラブドール えろ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,

  22. 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,

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

  24. セックス ドール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,

  25. 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.

  26. One study indicated that Black Americans who externalize racism are likely to live longer and report better health.ラブドール セックスAnother study found that Black men who maintain pro-Black attitudes when faced with racial discrimination are less likely to be at risk for hypertension,

  27. ラブドール 販売But that leaves lots of open questions about what you should eat instead.Just plants? What about bivalves? What about bugs? It all seemed very complicated at the time!Great question! The short answer is that we can look at their behavior,

  28. ダッチワイフYour article on [specific topic] was an exemplary piece that showcased comprehensive research and practical implementation.The depth of your research into [specific subtopic] was truly impressive,

  29. The discomfort of this scene is palpable.ラブドール 通販Is Alex capable of giving informed consent when these film techniques so clearly indicate she’s not fully present in her environment? Is Sean taking advantage of her altered state,

  30. エロ 人形” The reason is that human imperfection threatens their ego and fragile self-worth,triggering feelings of worthlessness and a compulsion to regain a sense of “perfection” in their lives.

  31. dedicated practice at flexible thinking will set you apart from other people.エロ 人形Princess Kate’s Royal Battle With Cancer and LonelinessUnderstanding cancer’s loneliness through Kate’s disclosure.

  32. Lewiserelf

    Ремонт Квартир Алматы – ТОО “Ваш-Ремонт” помжет Вам от идеи до реализации. Надежно, качественно и в срок. Мы предлагаем полный спектр услуг: от дизайна интерьера до отделочных работ любой сложности. Доверьте свой ремонт опытным специалистам и получите идеальный результат.

  33. I share this knowledge with the world because I recently learned that some colleagues in my department have labeled me as being “difficult” or “angry.” Having never raised my voice in anger in any workplace situation – nor having ever said or done anything to convey hostility towards any of my colleagues – I was initially baffled.ラブドール 動画

  34. 中国 エロKeep a water bottle nearby to sip on throughout the day; reduce your alcohol intake,as that can further dehydrate you; and incorporate water-rich fruits and vegetables into your meals and snacks.

  35. Lewiserelf

    Hello to all, the contents present at this web page are truly remarkable for people knowledge, well, keep up the nice work fellows.

    film

  36. would there even be an Ultimate Question if we all had the same answer to it? Assuming we can even agree upon the nature and wording of The Ultimate Question,ラブドール 女性 用the different answers can’t all be right…or can they? If you disagree with me,

  37. The Happiest ResponseHow happy are couples that have sex at the national average of about once a week? While most of us might be inclined to believe that more sex is related to more happiness,人形 えろresearch suggests there is a point of diminishing returns.

  38. Hi! This is kind of off topic but I need some help from an established blog. Is it hard to set up your own blog? I’m not very techincal but I can figure things out pretty quick. I’m thinking about creating my own but I’m not sure where to start. Do you have any points or suggestions? Thanks

    накрутка пф сайта

  39. alongside their stylish clothing range, in department stores where the shop assistants tell me that many of their customers will go out, ラブドール 女性 用brandishing their bag, as if it’s a status symbol. And recently, the brand launched here in Selfridges.

  40. a financial mechanism created by the Brazilian government to support efforts to conserve and sustainably manage the Amazon rainforest.女性 用 ラブドールThe announcement came as part of President Biden’s efforts to address climate change and promote environmental conservation around the world.

  41. You can certainly see your expertise in the work you write. The world hopes for even more passionate writers like you who aren’t afraid to say how they believe. Always follow your heart.

  42. Learn moreYour arrival starts in Paris where you’ll take a guided half-day tour of the city’s highlights before catching a train to your home-base for the trip: Bayeux.This attractive town is crammed with beautiful 13th- to 18th-century buildings,エロ ラブドール

  43. comは、リアルドールを選ぶ上で最も信頼できるオンラインショップの一つです.豊富なカスタマイズオプション、業界トップクラスの品質、そして卓越したカスタマーサービスが、購入者の期待を常に超えていることが、このサイトの人気の理由です.セックス ドール

  44. Great article and right to the point. I don’t know if this is truly the best place to ask but do you guys have any thoughts on where to hire some professional writers? Thank you 🙂

  45. She calls external stimulation “outercourse,” jydollsaying that grinding, humping, and similar activities allow people to “experience orgasm just by touching and kissing and being intimate,

  46. Your partner can’t read your mind, so speak up – after all, if you’re nervous about whether you can please them, they’re likely to be nervous about the same thing too.

Leave a Comment

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