Linqpad not listing all the stored procedures
I also tried listing the sprocs using this query and it shows that the missing ones and the shown ones both have the same schema.
SELECT specific_schema, routine_schema, specific_name FROM information_schema.routines
WHERE routine_type = 'PROCEDURE'
ORDER BY specific_name
Comments
-
Can you provide the DDL for a simple repro?
-
We're seeing this issue with Stored Procs that use custom Table Types. The interesting thing is that LINQPad 7 works properly, but LP5 and LP8 don't. (All licensed)
LP7 shows a Table Types section with our custom types, and all the stored procs show properly.
Both LP5 and LP8 do not show a Table Types section, and don't show the stored procs that use them (but others show fine). So there is a clear regression from LP7 to LP8.
Everything is in one schema (dbo), and there is no duplication of names anywhere.
Should be very easy to repro, but if I can assist, let me know. -
Can you provide a MRE? A simple script that I can run on SQL Server to reproduce the problem.
-
Sure:
Here's a contrived (and poorly normalized) db that shows it:CREATE DATABASE [LP_SP_Quiz] CONTAINMENT = NONE ON PRIMARY ( NAME = N'LP_SP_Quiz', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\LP_SP_Quiz.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) LOG ON ( NAME = N'LP_SP_Quiz_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\LP_SP_Quiz_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) GO USE [LP_SP_Quiz] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Students]( [Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, [Name] varchar(200) NOT NULL ) CREATE TABLE [dbo].[Quiz]( [Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, [Name] varchar(100) NOT NULL ) CREATE TABLE [dbo].[QuizQuestionAnswers]( [Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, [QuizId] [int] NOT NULL, [QuestionNum] [int] NOT NULL, [QuestionText] varchar(200) NOT NULL, [AnswerNum] [int] NOT NULL, [AnswerText] varchar(200) NOT NULL ) ALTER TABLE [dbo].[QuizQuestionAnswers] WITH CHECK ADD CONSTRAINT [FK_QuizQuestionAnswers_Quiz] FOREIGN KEY([QuizId]) REFERENCES [dbo].[Quiz] ([Id]) GO CREATE TABLE [dbo].[StudentQuizAnswers]( [Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, [StudentId] [int] NOT NULL, [QuizId] [int] NOT NULL, [QuestionNum] [smallint] NOT NULL, [AnswerNum] [smallint] NOT NULL, ) GO ALTER TABLE [dbo].[StudentQuizAnswers] WITH CHECK ADD CONSTRAINT [FK_StudentQuizAnswers_Student] FOREIGN KEY([StudentId]) REFERENCES [dbo].[Students] ([Id]) GO ALTER TABLE [dbo].[StudentQuizAnswers] WITH CHECK ADD CONSTRAINT [FK_StudentQuizAnswers_Quiz] FOREIGN KEY([QuizId]) REFERENCES [dbo].[Quiz] ([Id]) GO GO CREATE TYPE [dbo].[QuestionAnswerList] AS TABLE ( -- Will show in LP 7, but not LP 5 or 8 [QuestionId] SMALLINT NULL, [AnswerId] SMALLINT NULL); GO CREATE PROCEDURE [dbo].[SetStudentName] -- Will show in 5, 7, and 8. @studentId INT, @studentName varchar(200) AS SET NOCOUNT ON; UPDATE [Students] SET [Name] = @studentName WHERE [Id] = @studentId GO CREATE PROCEDURE [dbo].[SetStudentQuizAnswers] -- Will show in LP 7, but not LP 5 or 8 @studentId INT, @quizId INT, @questionAnswers QuestionAnswerList READONLY -- The use of a custom type causes LP 5 and 8 issues apparently. AS SET NOCOUNT ON; BEGIN TRANSACTION -- Delete answers that have been removed (in DB but not passed in) DELETE currentAnswers FROM dbo.[StudentQuizAnswers] AS currentAnswers LEFT JOIN @questionAnswers AS newAnswers ON newAnswers.QuestionId = currentAnswers.QuestionNum AND newAnswers.AnswerId = currentAnswers.AnswerNum WHERE currentAnswers.StudentId = @studentId AND currentAnswers.QuizId = @quizId AND newAnswers.AnswerId IS NULL; -- Insert answers that have been added (passed in but but not in DB) INSERT dbo.[StudentQuizAnswers] (StudentId, QuizId, QuestionNum, AnswerNum) SELECT @studentId, @quizId, newAnswers.QuestionId, newAnswers.AnswerId FROM @questionAnswers AS newAnswers LEFT JOIN dbo.[StudentQuizAnswers] AS currentAnswers ON currentAnswers.StudentId = @studentId AND currentAnswers.QuizId = @quizId AND currentAnswers.QuestionNum = newAnswers.QuestionId AND currentAnswers.AnswerNum = newAnswers.AnswerId WHERE currentAnswers.AnswerNum IS NULL COMMIT TRANSACTION RETURN 0 GO
-
Thanks for the repro. This is caused by a strange bug in SQL Server, whereby user-generated table types report as system-generated. There will be a workaround in the next beta; in the meantime, you can avoid this problem by checking the 'Include System Tables/Views' option in the connection dialog.