Posted by: Colin Smith
Dynamic SQL, T-SQL
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.