MS SQL Search within programmable objects (sp, trigger, view,...)

Below script searches keywords within programmable objects in all databases.

declare @keyword1 varchar(50) = 'SomeTable'
declare @keyword2 varchar(50) = 'SomeColumn'

create table #t
(
 [ReferencingDatabase] varchar(100),
 [ReferencingSchema] varchar(100),
 [ReferencingObject] varchar(100),
 [ReferencingObjectType] varchar(100),
 [HelpText] varchar(300)
)

declare @cmd1 varchar(8000)
set @cmd1 = '
use ?
if ''?'' not in (''distribution'', ''master'', ''model'', ''msdb'', ''tempdb'', ''mssqlsystemresource'')
begin
insert into #t
select
''?'' as [ReferencingDatabase],
s.name as [ReferencingSchema],
o.name as [ReferencingObject],
o.type_desc as [ReferencingObjectType],
''sp_helptext '''''' + s.Name + ''.'' + o.name + '''''''' as [Script]
from sys.syscomments c
left outer join sys.objects o on o.object_id = c.id
left outer join sys.schemas s on s.schema_id = o.schema_id
where
c.text like ''%' + @keyword1 + '%''
and c.text like ''%' + @keyword2 + '%''
end
'
exec sp_msforeachdb @cmd1
select * from #t
drop table #t

Find dependant objects in MS SQL

Below script finds dependant objects for a given object.


declare @server varchar(50) = 'sql server name'
declare @db varchar(50) = 'database name'
declare @schema varchar(50) = 'schema name'
declare @object varchar(50) = 'object name'

create table #t
(
[ReferencingDatabase] varchar(100),
[ReferencingSchema] varchar(100),
[ReferencingObject] varchar(100),
[ReferencingObjectType] varchar(100)
)

declare @cmd1 varchar(8000)
set @cmd1 = '
if ''?'' not in (''distribution'', ''master'', ''model'', ''msdb'', ''tempdb'', ''mssqlsystemresource'')
begin
insert into #t
select
 ''?'' as [ReferencingObjectDatabase],
 s.name as [ReferencingObjectSchema],
 o.name as [ReferencingObjectName],
 o.type_desc as [ReferencingObjectType]
from ?.sys.sql_expression_dependencies d
inner join ?.sys.objects o on o.object_id = d.referencing_id
inner join ?.sys.schemas s on s.schema_id = o.schema_id
where d.referenced_entity_name = ''' + @object + '''
and (d.referenced_server_name = ''' + @server + ''' or d.referenced_server_name is null)
and (d.referenced_database_name = ''' + @db + ''' or d.referenced_database_name is null)
and (d.referenced_schema_name = ''' + @schema + ''' or d.referenced_schema_name = '''' or d.referenced_schema_name is null)
end
'

exec sp_msforeachdb @cmd1
select * from #t
drop table #t

SOA Tips

I learned some tips about implementing service oriented architecture by experiencing the difficulties that emerged because we didn't care about them.

A. SOA does not mean that you're not going to do object oriented design.

In order to avoid complexity, you should design the objects in your business domains.

If you do not, SOA eventually becomes procedural programming. You call methods from the client one by one.

B. Each client should call only one service method to do one job. Otherwise:

Round trip increases: cost of opening a connection from client and sending data through network is much much more than you think. Doing it multiple times for a single job makes your software incredibly slow.

Because services run stateless (at least our implementation does so) every time you get a call at the service, you instantiate all the data to do your job again and again. Take all the data you need to do a job, then do it at once.

TFS vNext needs audit log reports

Because of some governmental regulations, we need to report the administrative actions over permissions on TFS. Auditors frequently request such reports from us.

I requested the audit logs of administrative actions over permissions on TFS from Microsoft in the VS vNext Wish List.

We'll see what Microsoft thinks about adding audit reports to TFS in the next version. What do you think, is it worth?

TFS Case Study of Microsoft Turkiye

Microsoft Turkiye examined our TFS implementation and published a case study document:
https://blog.microsoft.com.tr/turkiye-finans-katilim-bankasi.html

Unfortunately the document is prepared only for Turkish audience, it has no English translation.

Count lines of code in a database

Here is a small script to count lines of code in a database:

select
    o.type_desc as ObjectType
    ,count(*) as [ObjectCount]
    ,sum(len(m.definition) - len(replace(m.definition, char(13), ''))) as [TotalLineCount]
from sys.objects o
inner join sys.sql_modules m on o.object_id = m.object_id
where o.[type] in ('P', 'TR', 'FN', 'IF', 'V', 'TF')
and o.name not like 'aspnet[_]%'
and o.name not like 'vw[_]aspnet[_]%'
group by o.type_desc
order by o.type_desc

Sign a .Net assembly without the source code

Two different versions of an old .Net assembly caused some deployment problems. We wanted to deploy the assembly to the GAC but neither of the two versions were signed. We needed to sign the assemblies but we did not have the source code.

After a small search I found a great tool named ILMerge.

Main purpose of ILMerge is to combine more than one .Net assemblies into one assembly. The side benefit is that you can also sign the resulting assembly. It works while working with only one assembly too. This way you can sign an assembly without having the source code.

Sample command line:

ilmerge /t:library /keyfile:"c:\SomeKeyFile.snk" /out:"c:\signed\assembly.dll" "c:\unsigned\assembly.dll"