Dependencies

/*
Ever tried to use the sysdepends table to determine dependencies among your database objects? It doesn't ALWAYS work. There are those occasions when during creation, you might get a warning message from SQL Server that says that a procedure was created but that the row was not added to sysdepends:


Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'spGetSQLPoetsStats. The stored procedure
will still be created.


I think we have all had this issue and know why it happens. The purpose of this post is to show you another way to 'skin-the-cat'.


Take for instance this undocumented procedure: sp_MSdependencies. If you Googled it you might encounter some difficulty in finding just how to use it. It is one of many undocumented tools that Microsoft 'provides' with SQL Server. It should be noted at this point that Microsoft does not guarantee future support for undocumented objects. It is recommended that you do not use this in your application code but simply as a means of adhoc reporting for as long as we can.


The task in this case was to provide the QA folks with a list of the objects they would be testing and the dependencies thereof. As stated earlier, sysdepends does not ALWAYS help and this was one of those times.


If you EXECUTE sp_msdependencies '?' in the query editor against sql server's master database you will find a list of parameter values you can specify. Pay particular attention to the flag parameter. Therein lies the magic:)

I am using 266751 which shows the objects on which these depend and also 4607 to show the objects that depend on those objects...confused? The result set should fill in the holes in your understanding.

*/

set nocount on

declare @tablename varchar(100)
declare @type varchar(100)
declare @ServerName varchar(100)

set @ServerName = db_name()

Declare @temp2 table
(ServerName varchar(100),
ObjectName varchar(100),
objecttype varchar(100),
Dependency varchar(100),
ObjectName_2 varchar(100),
Objecttype_2 varchar(100))

Declare @temp table
(otype varchar(100),
oObjname varchar(100),
oOwner varchar(50),
oSequence int)

Select top 1 @tablename = name, @Type = type_desc from sys.objects
where type in ('FN','P','TF','V','U')
and is_ms_shipped <> 1
order by name

while @tablename is not null
Begin

Insert into @temp
EXEC sp_MSdependencies @tablename, null, 266751 --this flag shows which objects depend on these

Insert into @temp2
select @ServerName, @tablename, @type, 'Is Used by', oObjname,
Case otype
When 4 then 'View'
When 16 then 'Stored Procedure'
When 256 then 'Trigger'
When 8 then 'Table'
When 1 then 'Function'
Else otype
end
from @temp

Delete from @temp

Insert into @temp
EXEC sp_MSdependencies @tablename, null, 4607 --this flag shows the objects these depend on

Insert into @temp2
select @ServerName, @tablename, @type, 'Is Dependent on', oObjname,
Case otype
When 4 then 'View'
When 16 then 'Stored Procedure'
When 256 then 'Trigger'
When 8 then 'Table'
When 1 then 'Function'
Else otype
end
from @temp

Delete from @temp
Print @tablename

set @type = (Select top 1 type_desc from sys.objects
where type in ('FN','P','TF','V','U')
and is_ms_shipped <> 1
and name > @tablename
order by name)

set @tablename = (Select top 1 name from sys.objects
where type in ('FN','P','TF','V','U')
and is_ms_shipped <> 1
and name > @tablename
order by name)

end

select * from @temp2 order by objecttype

/*
With the results of this script, we can provide the QA folks the list they need to document, test, etc.

Happy TSQLing
SQLPoet
*/

Using SQL to write SQL

/*
You may be asked to create users for your company's inhouse application. We're not talking SQL Server Logins and Database Users, no. We're talking security credentials that most web applications administer through tables seemingly common to most businesses like the [Users] table.

If you were to get hit with one of these requests, your first reaction might be to ask whether a process or scripts for this sort of thing already exist. Depending on the size and age of your company and the quality of your predecessor DBAs, there might not be any processes or scripts available and so you must create one. Sure you COULD go to the GUI and then script your changes out but you still have to manually enter the data values for the new record. Plus using the GUI is a much less efficient way to get these users into every environment in which they are needed (thanks to the human element and our tendency to error).

I recently got one of these requests in which the author of the ticket specified three new users to be created and modelled after another user (permission-wise). Our application keeps user information in a series of tables with the prefix User. In order for a user to have access to the application, they will need a record in the [User] table.

No brainer, so far?

To INSERT into the User table we might use something like:
*/

INSERT [User](User_Group_ID,User_Name,Password_Hash,Full_Name,Email_Address,[...])
VALUES ('3121', 'sqlpoet', 'B44DDA1DADD351948FCACE1856ED97366E679239',[...])

/*
Even with a manually written insert statement like this one we still have to get the data of the user we are required to model these new ones after. Since I did some preliminary querying on the the table, I know that our model user's User_ID = 11106
*/

SELECT * FROM [User] WHERE User_ID = 11106

/*
Using the data resulting from the query above, we plug in the common data values changing variable datavalues such as User_Name and then copy and paste each value into its cooresponding place in the INSERT statement.

But what if we re-wrote our SELECT to write the INSERT for us?
*/

SELECT

'INSERT [User](User_Group_ID,User_Name,Password_Hash,Full_Name,Email_Address,[...])

VALUES(''' + convert(varchar(20),User_Group_ID) + ''', ''' + [User_Name] + ''', ''' + char(10) +
Password_Hash + ''', ''' + Full_Name + ''', ''' + Email_Address + ''', ''' + [...] + ''')'

FROM [User] WHERE User_ID = 11106 --our model user

/*
The result of the above query, done correctly, will yield a statement that with a little editing can be copied and pasted into your change control script so that these users easily move from environment to environment whenever executed. Or you can execute them to create these users now...its up to you. Happy TSQLing.

SQL Poet
*/