Reputation: 3224
I created following powershell (v2) script to execute query on target database and build another query/queries to be run:
$sql = @"
DECLARE @Tables CURSOR;
DECLARE @TableName NVARCHAR(200);
BEGIN
SET @Tables = CURSOR FOR
SELECT TABLE_NAME
FROM test.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME LIKE 'x%'
OPEN @Tables
FETCH NEXT FROM @Tables
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'SELECT TOP 1 * FROM test.dbo.' + @TableName
FETCH NEXT FROM @Tables
INTO @TableName
END;
CLOSE @Tables ;
DEALLOCATE @Tables;
END;
"@
$ps = [PowerShell]::Create()
$ps.AddCommand("Invoke-Sqlcmd").AddParameter("Query", $sql).AddParameter("Verbose")
$ps.Invoke()
$sqlOutput = $ps.Streams.Verbose
$sqlOutputToRun = $nul
$sqlOutput | foreach {
$sqlOutputToRun += ($_.ToString() + "`n")
}
$sqlOutputToRun = @"
$sqlOutputToRun
"@
$sqlOutputToRun
In the end of script I am printing queries that have to be executed, but unfortunately variable $sqlOutputToRun contains only about 3/4 expected output. I suppose that output is somehow cut because of the size, but I am not sure how I can extend it.
Do you know what is missing?
Upvotes: 0
Views: 1028
Reputation: 89489
And once you dump the print
statements in favor of a resultset, you can dump the cursor too, and just run a simple query like:
select 'SELECT TOP (1) * FROM '+ quotename(table_catalog) + '.' + quotename(table_schema) + '.' + quotename(table_name)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME LIKE 'x%'
so in Powershell:
$sql = @"
select 'SELECT TOP (1) * FROM '+ quotename(table_catalog) + '.' + quotename(table_schema) + '.' + quotename(table_name)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
-- AND TABLE_NAME LIKE 'x%'
"@
$sqlOutputToRun = $nul
invoke-sqlcmd $sql | foreach {
$sqlOutputToRun += $_[0] + "`n"
}
$sqlOutputToRun
Upvotes: 1
Reputation: 1955
If you can't live without your cursor why you can't do something like this?
$sql = "
DECLARE @Tables CURSOR;
DECLARE @TableName NVARCHAR(200);
DECLARE @Results TABLE (ResultQuery NVARCHAR(MAX))
BEGIN
SET @Tables = CURSOR FOR
SELECT TABLE_NAME
FROM testdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
--AND TABLE_NAME LIKE 'x%'
OPEN @Tables
FETCH NEXT FROM @Tables
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @Results (ResultQuery)
SELECT 'SELECT TOP 1 * FROM test.dbo.' + @TableName
-- PRINT 'SELECT TOP 1 * FROM test.dbo.' + @TableName
FETCH NEXT FROM @Tables
INTO @TableName
END;
CLOSE @Tables ;
DEALLOCATE @Tables;
END;
SELECT * FROM @Results;"
$sqlOutputToRun = Invoke-Sqlcmd -query $sql
$sqlOutputToRun
Upvotes: 1