I was always challenged when my customers asked me what tables and columns are referenced by a stored procedure which was written many years ago by the guy who left the company 5 years ago. When I Google the solution, I was always told that sys.sql_dependencies
and sys.sql_expression_dependencies
can tell. At the end of the day, I figured that depending on the complexity of the procedure, those 2 views couldn’t always give me accurate information as needed, dynamic SQLs for instance. Even worse, my customer also asked me if a table was accessed by anyone and what columns were referenced. I realized that I have to write something to get it done.
Continue reading “Get All Referenced Tables and Columns”
Print Long String
Quite often, I output information through PRINT command. It works well only when message is shorter than 8000 bytes. When the message is greater than 8000 bytes, extra-characters will be removed. the simplest way to overcome this is to make a loop and print 8000 characters at a time
declare @line nvarchar(max) = Replicate(cast('0123456789' as varchar(max)), 800)+'abc ' select @line = replicate(@line, 3) declare @pos int = 1 while @pos <= len(@line) begin print substring(@line, @pos, 8000) select @pos = @pos + 8000 end /* 012345678901234567890.... abc 012345678901234567890.... 6789abc 012345678901234567890.... 23456789abc */