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 am lost. Could you expand a bit on what is your ultimate goal, before dwelling into the details please?
Why do you want to re-create the report in powershell if you already have the code in t-sql? You can connect from powershell to the sql, run the query/procedure and just consume the results. Then you can do what ever you want with them. Serialize them to csv, print it as html report.
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.
Powered by IDERA