Hi experts I would like to ask help on writing Powershell script for below.
First I would like to Select the Database. Second Create a Temp Table Third I would like to Get the Two tables like this @DBName+'.dbo.sp_helpuser' Fourth Then insert the data in the Temperary table
set @ dbname =Select name from master.dbo.sysdatabases where Name not in ('tempdb','Pubs','Northwind','Model') -- get DB info If (Select isnull(object_id('tempdb..#DBUser'), 0)) = 0 Create table #DBUser_08 ( UserName varchar(150) null, RoleName Varchar(1000) null, LoginName Varchar(200) null, DefDB Varchar(1000) null, DefSchemaName varchar(500) null, UserID int null, SUserID int null ) Truncate table #DBUser Declare @DBQuery varchar(200) Set @DBQuery = @DBName+'.dbo.sp_helpuser' Insert into #DBUser exec (@DBQuery)
I find that converting real-world SQL scripts is a fun way to learn PowerShell and SMO.
Here is an approximation of what you're looking for. You can modify as you wish. This creates a custom object with most of the information that your original script contained. I didn't know what DbType referred to, however.
This took 486 milliseconds to return 57 results!
You can also find the code on gist.