Tag: store procedures

Where the hell is that column???

amikolaj Leave a comment

“Hey Database dude, I need you to update the PDADE33e433 Table with values for columns GrAnDaMa12333 and WTF32323 with values ‘ASdjendnd’ and ‘BarfFartwekljweljk3′. Cool? Thanks.’

I work with engineers who use databases. They design all the products that my company sells. They are really good at designing but…they use databases. For them, I am a necessary evil. We all get along very well but they would rather be designing products than deal with storing information. Did I mention it’s one of me to about 300 of them?

With those odds it’s very hard to keep track of what they are doing in their databases. With supporting over 100 databases (possibly more, only on month 3) I am still finding it a hard time to try to find out how they are building these databases (that’s a whole different blog entry).

What I do know is that I get requests to change data that they don’t have access to. When this happens I have to figure out where this data is. Most of the time they tell me a server and a table. I always say “Sure, no problem, just make sure you send me an email with the details”

This is where my journey begins. To save time I have built a stored proc that I have been installing in all the master DBs of all the instances. This SP checks all columns in all databases to see if one is LIKE the keyword that I pass in. Feel free to use it. NOTE: it also gives you view names.

SET ansi_nulls ON
GO
SET quoted_identifier ON
GO
/*
-- =============================================
-- Author:    Adam Mikolaj-- Create date: 19 May 2011
-- Description:  Use this SP to search for a column in every database on your server.
-- I created this because sometimes the I have no idea what database a column I need is in. 
-- This quickly searches all your DBs
-- Sample - exec uSP_WheresThatColumn 'Birthdate'
-- =============================================
*/
 
CREATE PROCEDURE Usp_wheresthatcolumn
 
@COLUMN VARCHAR(255)AS
BEGIN
SET nocount ON;
 
CREATE TABLE #whereisit(
database_name VARCHAR(255),
TABLE_NAME VARCHAR(255),
column_name VARCHAR(255))
 
DECLARE @SQL VARCHAR(MAX)
SET @SQL =
 'insert into #whereisit
 exec sp_MSforeachdb  ''
use [?] select ''''?'''' as DATABASE_NAME , TABLE_NAME, COLUMN_NAME 
from INFORMATION_SCHEMA.COLUMNS
 where COLUMN_NAME like ''''%'+@COLUMN+'%'''' and TABLE_NAME not like ''''#%''''
 ''
  '
 
EXEC (@SQL)
SELECT * FROM #whereisit
DROP TABLE #whereisit
END
GO

To call it just type in master..uSP_WheresThatColumn ‘column name’

Enjoy!

Adding domain users with TRY CATCH

amikolaj Leave a comment

I was recently given a task to give 20 people read permissions on a dev server. My organization uses AD groups to manage these on the production side of things but on the dev side, it’s a bit like the wild west.

I wasn’t sure if these users were already in the database or if they were new. I figured this was a good time to play around and create some sort of auto inserter for me. I wanted to be able to put the person’s user name in and wanted it to return if they were in the system or not. If they were, great, add them to this DB, if not, add them to the server and then DB.

I ran into the situation where there was a name on the list of people I had who’s username was wrong. Well…how can I check AD to make sure they were there without it blowing up? I couldn’t find a solution so I decided to use TRY…CATCH and grab the error.

Before anyone says anything about automatically entering users, this was just for my personal use and is not intended to be placed in any application.

Check it out:

USE MASTER
SET ansi_nulls ON
GO
SET quoted_identifier ON
GO
-- =============================================
 
-- Author:    Adam Mikolaj-- Create date: 12 May 2011-- Description:  Little stored procedure for entering in users
 
-- =============================================
 
CREATE PROCEDURE uSP_AutIinserter
 
 @USER VARCHAR (100),
 
@dbname VARCHAR(100),
 
@DOMAIN VARCHAR(100)
 
AS
BEGIN
 
DECLARE @LiveUser BIT,@Sqls VARCHAR(500)
 
IF EXISTS (SELECT *FROM syslogins
 
WHERE name LIKE '%' + @USER + '%')
 
BEGIN
 
SET @LiveUser = 1
 
END
 
ELSE
 
SET @LiveUser = 0
 
IF @LiveUser = 0
 
BEGIN
 
SET @Sqls = 'CREATE LOGIN [' + @DOMAIN + '\' + @user +'] 
 
FROM WINDOWS WITH DEFAULT_DATABASE=' +@DBName+''
 
BEGIN TRY
 
EXEC (@Sqls)
 
END TRY
 
BEGIN CATCH
 
SELECT Error_message() AS errormessage;
 
END CATCH
PRINT 'USER IS now IN the system' END
ELSE
PRINT 'USER already IN the system'END
 
GO

To call this procedure just do this:

uSP_AutoInserter ‘Dude’, ‘DB’, ‘domain’