Friday, November 16, 2012

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

Monday, November 5, 2012

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

Tuesday, March 13, 2012

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.

  •