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

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

  286. ラブドール

    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,

  329. ダッチワイフ

    ラブドール 販売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,

  348. オナホ

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

  364. ラブドール

    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,

  366. ラブドール

    エロ 人形” 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.

  369. ダッチワイフ

    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.

  372. Lewiserelf

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

  380. ラブドール

    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.ラブドール 動画

  399. えろ 人形

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

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

  444. ラブドール

    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,

  448. ラブドール オナホ

    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.

  457. Shanenen

    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

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

  463. ラブドール

    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.

  475. エロドール

    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.

  490. alquiler moto ibiza low cost

    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.

  498. ラブドール えろ

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

  506. ラブドール 巨乳

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

  509. tlovertonet

    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 🙂

  513. オナホドール

    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,

  515. せっくす 美人

    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.

  520. FobertEcova

    I’m not sure why but this web site is loading extremely slow for me. Is anyone else having this problem or is it a issue on my end? I’ll check back later on and see if the problem still exists.
    best online casino Australia

  521. ラブドール

    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,

  537. ラブドール

    Unexplained bruises may be indicative of a sexually abusive relationship STIs or pregnancy when the partner is unidentifiedBehavioral signs of childhood sexual abuse: Inadequate personal hygiene Drug and alcohol abuse Sexual promiscuity Running away from homeEmotional signs of childhood sexual abuse: Self-injury (cutting,burning) Thinks of self or body as repulsive,ラブドール 高級

  554. 人形 エロ

    結論から言えば警察がアウトと判断したら捜査が行われ、セックス ロボット検察がアウトと判断したら起訴が行われ、裁判所がアウトと判断したら法で裁かれるということになります。

  556. 人形 エロ

    当社のオンライン ストアではラブドール 中古快楽のドール世界に足を踏み入れることができ、ワンクリックで、等身大ドールやアクセサリーを購入するのに最適な場所であることがわかります。

  558. ラブドール

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

  562. FobertEcova

    What’s up Dear, are you genuinely visiting this web site on a regular basis, if so then you will absolutely get nice know-how.

    Brand Technology

  565. sexy fox costume

    as you’ll be so well-informed from reading all of our tips here! And not only that,velma cosplay sexybut you can tell the recipient that their silk ruffled panties are completely handcrafted with our ethical manufacturing guarantee.

  567. オナドール

    各ドールは、最高品質の素材を使用して精密に作られており、そのリアルさと耐久性には感動を覚えます.肌の質感は非常に柔らかく、リアルな触感があり、顔のディテールは非常に精密で、人間の美しさを完璧に再現しています.中国 えろ

  568. sexy velma

    sexy velmaFor Love & Lemons x Victoria’s Secret Izzy Babydoll Slip DressThe effortlessly elegant silhouette of this babydoll slip is both flirty and super comfy.Made with sheer light blue fabric,

  577. sexy velma

    sexy velma cosplaywhile still having the breathability to avoid uncomfortable overheating.Take advantage by layering your silks with pieces made in other natural fibers with even more heat retention properties – add a cashmere cardigan on top of a silk cami for cozy work from home comfort,

  587. Lewiserelf

    Thank you for sharing your info. I truly appreciate your efforts and I will be waiting for your next write ups thanks once again.
    cigars-shops.ru

  592. Kieth Clara

    I will right away snatch your rss feed as I can’t in finding your e-mail subscription link or e-newsletter service. Do you’ve any? Please permit me recognise in order that I may subscribe. Thanks.

  609. NBA streaming site

    This is really fascinating, You’re an excessively skilled blogger. I have joined your feed and sit up for looking for extra of your excellent post. Also, I’ve shared your site in my social networks!

  610. GullyBET

    Great beat ! I wish to apprentice at the same time as you amend your web site, how could i subscribe for a blog web site? The account helped me a acceptable deal. I have been a little bit familiar of this your broadcast provided brilliant transparent concept

  611. Live NBA games today

    I think other web site proprietors should take this web site as an model, very clean and wonderful user genial style and design, as well as the content. You are an expert in this topic!

  621. seo experts us

    This is very interesting, You’re an overly professional blogger. I have joined your feed and look forward to in the hunt for extra of your great post. Also, I’ve shared your website in my social networks!

  622. Lhanenen

    Thanks , I have just been looking for information approximately this subject for a long time and yours is the best I’ve found out so far. But, what in regards to the bottom line? Are you certain concerning the source?
    смотреть кино онлайн

  640. Shanenen

    Simply desire to say your article is as amazing. The clarity in your post is simply great and i could assume you are an expert on this subject. Well with your permission allow me to grab your RSS feed to keep up to date with forthcoming post. Thanks a million and please continue the rewarding work.

    http://compromat.in.ua/2-5-inch-bilead-lens-guide.html

  648. CBD Online Kaufen

    whoah this blog is great i love reading your posts. Keep up the good work! You know, lots of people are looking around for this information, you can help them greatly.

  656. droversointeru

    naturally like your website however you have to test the spelling on several of your posts. Several of them are rife with spelling issues and I to find it very bothersome to tell the truth however I will surely come again again.

  663. オナドール

    division chief,中国 エロOb/Gyn Behavioral Medicine at the University Hospitals Cleveland Medical Center and codirector of the Sexual Medicine and Vulvovaginal Health Program at the UH Cleveland Medical Center.

  672. 最 高級 ダッチワイフ

    Singapore feels similar to New York in this way: not only is it a glass-towered metropolis of wealth; it’s also a hotbed for a half dozen or so different ethnic groups making—and cooking—their way through the world.Some fascinating cultural fusion has emerged as a result.ラブドール 高級

Leave a Comment

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