Have you ever needed to find all of the tables in a database that contain a field called _____ (insert field name here). It’s actually pretty easy to do. Microsoft was kind enough to provide us with a number of system catalog views that give us a plethora of useful information. One of these views is “sys.columns”. You can use sys.columns to figure out every table that has a column with a specific name.

For example. Let’s say you had a column called ‘ApplicationId’ on a specific table and you wanted to change the length of that column from varchar(10) to varchar(15). Chances are your application may use this field on several different tables. If this is the case then obviously you would want to make sure that you changed this field on every table that uses this field to prevent your application from errors if it attempts to insert a 15 character value into this field in any given table. You know of a few tables that have this ‘ApplicationId’ field, but you’re getting older and your memory just isn’t what it used to be. So to make sure that you get all of them you can use sys.columns like this:

-----------------------------------------------------------------------
select object_name([object_id]), * from sys.columns 
where name = 'ApplicationId'
order by object_name([object_id])
-----------------------------------------------------------------------

Notice that I used the system function “object_name()” to get the table name. Using the object_name() function gives me the table name without having to join to the sys.objects or sys.tables system catalog views to get the name. Of course if you needed more information about that table you could make this join on object_id like this:

-------------------------------------------------------------------------------------------
select a.name, b.name, b.[type], b.type_desc, b.[schema_id], schema_name(b.[schema_id])
from sys.columns as [a], sys.objects as [b]
where a.name = 'ApplicationId' and b.[object_id] = a.[object_id]
order by b.name
-------------------------------------------------------------------------------------------

This alows me to gather a little more information about the table that the field resides on such as: “is it a view instead of an actual table”.

Question: How do I write a SQL script that will return the current database name in SQL Server?

Answer: Easy – select db_name();

The system function db_name() can be used to return this information.

The syntax for this command is: “DB_NAME ( [ database_id ] )” where database_id is the database id from sys.databases. If do not pass a value to the database_id parameter the DB_NAME function will return the name of the database that the script is currently executing in.

The following example returns the name of the current database.

---------------------------------------
select db_name() as [Current Database];
go
---------------------------------------

The following example returns the value “AdventureWorks” which is the name of the current database.

---------------------------------------
use AdventureWorks;
go
select db_name() as [Current Database];
go
---------------------------------------

If you have any given database id and want to know the corresponding database name all you have to do is pass the database id to the db_name() system function. On my sql server the AdventureWorks database has the dababase id ’13’ (This can be found by selecting from the sys.databases system catalog view or by using the db_id() system function).

The following example returns the name of the database with the database id of ’13’. Since the AdventureWorks database on my particular SQL server has database id ’13’ the following example will return ‘AdventureWorks’ regardless of which database I am in when I execute the query.

--------------------------------------
select db_name(13) as [Database Name];
go
--------------------------------------

Have you ever wanted to know all of the SQL roles that a given login resides in within any database on a SQL server?  While gathering this information have you ever made the determination that using the graphical interface provided in SSMS to gather this information is cumbersome?

Well… I have.  Because of that I threw this script together.  The below script will return all of the database roles that a given login resides in within any database on the SQL server.  it’s actually a pretty simple little script. It uses a variable called @login which you populate with the login name that you want to search for.  By using a cursor it will move through each database on the server matching the database user with the sid from sys.server_principals and checking the roles to see where the login is hanging out.  It inserts the information into a temp table and then returns the temp table at the end.

System dmv used by this script:

• sys.databases ** – Contains one row per database in the instance of Microsoft SQL Server. http://msdn.microsoft.com/en-us/library/ms178534.aspx
• sys.server_principals ** – Contains a row for every server-level principal.  http://msdn.microsoft.com/en-us/library/ms188786.aspx
• sys.database_principals ** – Returns a row for each principal in a database. http://msdn.microsoft.com/en-us/library/ms187328.aspx
• sys.database_role_members ** – Returns one row for each member of each database role. http://msdn.microsoft.com/en-us/library/ms189780.aspx

use master;
go
set nocount on;
go

if (select object_id('tempdb..#temptable')) is not null
begin
  drop table #temptable;
end;
create table #temptable(
  [dbname] varchar(128) not null
, [member_name] varchar(128) not null
, [role_name] varchar(128) not null
);

declare @dbname varchar(128), @sql varchar(max), @login varchar(128), @sid varbinary(85), @server_principal_id int;
set @login = 'testlogin'

select @sid = [sid] from sys.server_principals where name = @login;

declare cursor1 cursor for
select rtrim(name) from sys.databases where state = 0;
open cursor1;
fetch next from cursor1 into @dbname;
while @@fetch_status = 0
begin
   set @sql = 'select ''' + @dbname + ''' as [dbname], a.name as [member_name], c.name as [role_name]
   from [' + @dbname + '].[sys].[database_principals] as [a], [' + @dbname + '].[sys].[database_role_members] as [b], [' + @dbname + '].[sys].[database_principals] as [c]
   where a.[sid] = ' + sys.fn_varbintohexstr(@sid) + ' and b.member_principal_id = a.principal_id and c.principal_id = b.role_principal_id'
   insert into #temptable exec(@sql);

   fetch next from cursor1 into @dbname;
end
close cursor1;
deallocate cursor1;

select * from #temptable order by dbname;