The Multifunctioning DBA

Sep 28 2011   1:27PM GMT

Dynamic SQL

Colin Smith Colin Smith Profile: Colin Smith

The other day I wanted to write a query that would create my actual query in the results pane. I wanted to generate the sql to delete all the logins on the instance that met a certain criteria. Well I had some trouble but I worked it out and once I did I thought wow that was way easier than I was making it. So I thought a post might help a few people.

So say I wanted to create the sql to drop the logins for all logins that were like ‘%smi%’. Well I was doing something like this:

select ‘drop login ‘ + (select name from sys.syslogns where name like ‘%smi%’)

If you run the subselect all alone it will return all the logins that you are looking for, however if you do it like this you will get an error that the subselect returned more than 1 result and that is not allowed. This was very frustrating to me but then it hit me…. So I did the following:

select ‘drop login ‘ + name from sys.syslogins where name like ‘%smi%’

and that gives you the drop statements in the results pane.

So all you have to do is run your actual select statement to get the names but add the text you want around it.

Hoe that helps.

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: