June 24, 2019 at 12:00 am
Comments posted to this topic are about the item Exporting Stored Procedure Results to a Table
June 24, 2019 at 9:47 am
which version of Management Studio are you using? I can't get this option, with Sql Server 2012
June 24, 2019 at 11:57 am
I think it is either an option she has created herself, or from an add-in product like ssmstoolspack og ssmsboost, i cannot recall this ever to have been a part of the standard SSMS
i use SSMSBoost, they have a similar option:
regards Peter
June 24, 2019 at 12:10 pm
I am using SQL Server 2016, but I do have the SSMS Tools Pack. I should have mentioned that in the post. Sorry for any confusion!
June 24, 2019 at 12:52 pm
Ran into the same issue, but I have SSMS Tools Pack and do not have the 'Script as INSERT' option. I do have the save as Excel and script or search grid results, but this will not give me the table. I am using ver. 4.9.6, and noticed they have a 5.0.0 release. Do you think this is another tool, as I did not notice the Excel or search options in your context menu?
June 24, 2019 at 12:53 pm
Never you mind, found it, it's a SQL Prompt by Redgate feature...
June 24, 2019 at 2:05 pm
You can also use the Import/Export wizard to generate the table structure if you don't have the SSMS tools pack or SQL prompt installed.
June 24, 2019 at 5:23 pm
You can also potentially pull the table information from tempdb.information_schema.columns and build the CREATE script from that. More steps, but reasonably quick after you've done it a time or two.
June 24, 2019 at 6:08 pm
Hi,
There is a small mistake in your code, as written the code will run forever as the loop variable is not incremented. You need a Begin Statement after the While and End statement after the Set @i....
DECLARE @i INT = 0;
WHILE @i < 200
INSERT INTO #RowCountTablePrelim
SELECT '[' + DB_NAME() + N']' + '.' + '[' + SCHEMA_NAME(schema_id) + ']' + '.' + '[' + t.name + ']' AS TableName,
SUM(ps.row_count) AS [RowCount]
FROM sys.tables AS t
INNER JOIN sys.dm_db_partition_stats AS ps
ON t.object_id = ps.object_id
AND ps.index_id < 2
AND ps.row_count > 5000
GROUP BY t.schema_id,
t.name,
ps.row_count;
SET @i = @i + 1;
June 24, 2019 at 6:31 pm
Hi Rick,
I had actually forgotten the BEGIN and END statements to the loop, which as you pointed out, would make it run indefinitely (I had incremented the loop as you suggested - please review code). Please see revised code below. It will not only work, but will also count the loop for you in the Messages tab. Thank you for bringing it to my attention!
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#IndexInfo') IS NOT NULL
DROP TABLE #IndexInfo;
IF OBJECT_ID('tempdb..#HoldingTable') IS NOT NULL
DROP TABLE #HoldingTable;
IF OBJECT_ID('tempdb..#RowCountTablePrelim') IS NOT NULL
DROP TABLE #RowCountTablePrelim;
IF OBJECT_ID('tempdb..#RowCountTableFinal') IS NOT NULL
DROP TABLE #RowCountTableFinal;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
CREATE TABLE #RowCountTablePrelim
(
TableName sysname NULL,
[RowCount] BIGINT NULL
);
CREATE TABLE #RowCountTableFinal
(
TableName sysname NULL,
[RowCount] BIGINT NULL
);
USE AdventureWorks2012;
GO
DECLARE @i INT = 0;
WHILE @i < 200
BEGIN
PRINT @i;
INSERT INTO #RowCountTablePrelim
SELECT '[' + DB_NAME() + N']' + '.' + '[' + SCHEMA_NAME(schema_id) + ']' + '.' + '[' + t.name + ']' AS TableName,
SUM(ps.row_count) AS [RowCount]
FROM sys.tables AS t
INNER JOIN sys.dm_db_partition_stats AS ps
ON t.object_id = ps.object_id
AND ps.index_id < 2
AND ps.row_count > 5000
GROUP BY t.schema_id,
t.name,
ps.row_count;
SET @i = @i + 1;
END
INSERT INTO #RowCountTableFinal
(
TableName,
[RowCount]
)
SELECT TableName,
SUM([RowCount])
FROM #RowCountTablePrelim
GROUP BY ROLLUP(TableName);
SELECT *
FROM #RowCountTablePrelim
ORDER BY [RowCount] DESC;
DROP TABLE #RowCountTableFinal;
DROP TABLE #RowCountTablePrelim;
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply