Monthly Archives: November 2018
Query um eine UI Tabelle darzustellen. Mit Filter pro Spalte, Sortierung pro Spalte, variablen Paging und Anzahl Zeilen ohne Filterung
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
DECLARE @Page INT = 1 , @PageSize INT = 50 , @LanguageID INT = 46 , @ObjectID UNIQUEIDENTIFIER = '27461EA1-6EAC-47C5-8500-50A411C440D6' , @Sort_Key BIT = NULL , @Sort_Default BIT = NULL , @Sort_Translation BIT = NULL , @Filter_Key NVARCHAR(50) = NULL , @Filter_Default NVARCHAR(255) = NULL , @Filter_Translation NVARCHAR(MAX) = NULL SELECT [KeyStore].[ID] AS [KeyStore_ID] , [KeyStore].[Key] AS [KeyStore_Key] , [KeyStore].[Default] AS [KeyStore_Default] , [KeyStore].[ApplicationID] AS [KeyStore_ApplicationID] , [Value].[ID] AS [Value_ID] , [Value].[Value] AS [Trans] , [Value].[LanguageID] AS [Value_LanguageID] , [Language].[EnglishName] AS [Language_EnglishName] , TotalRows = COUNT(*) OVER() -- Return Total Rows. Without Offset FROM [Translation].[KeyStore] LEFT JOIN [Translation].[Value] [Value] ON [Value].[KeyStoreID] = [KeyStore].[ID] INNER JOIN [Translation].[Language] [Language] ON [Language].[ID] = @LanguageID WHERE ([ApplicationID] = @ObjectID AND ([Value].LanguageID IS NULL OR [Value].LanguageID = @LanguageID)) -- Filter: Key AND ( (@Filter_Key IS NULL OR @Filter_Key = '') OR ([KeyStore].[Key] LIKE REPLACE(@Filter_Key, '*', '%') OR (DIFFERENCE([KeyStore].[Key], @Filter_Key) > 3) ) ) -- Filter: Default AND ( (@Filter_Default IS NULL OR @Filter_Default = '') OR ([KeyStore].[Default] LIKE REPLACE(@Filter_Default, '*', '%') OR (DIFFERENCE([KeyStore].[Default], @Filter_Default) > 3) ) ) -- Filter: Translation AND ( (@Filter_Translation IS NULL OR @Filter_Translation = '') OR ([Value].[Value] LIKE REPLACE(@Filter_Translation, '*', '%') OR (DIFFERENCE([Value].[Value], @Filter_Translation) > 3) ) ) ORDER BY -- Sort: Key CASE @Sort_Key WHEN 0 THEN [KeyStore].[Key] END DESC, CASE @Sort_Key WHEN 1 THEN [KeyStore].[Key] END ASC -- Sort: Default , CASE @Sort_Default WHEN 0 THEN [KeyStore].[Default] END DESC, CASE @Sort_Default WHEN 1 THEN [KeyStore].[Default] END ASC -- Sort: Value , CASE @Sort_Translation WHEN 0 THEN [Value].[Value] END DESC, CASE @Sort_Translation WHEN 1 THEN [Value].[Value] END ASC -- Default Sort , CASE WHEN @Sort_Key IS NULL AND @Sort_Default IS NULL AND @Sort_Translation IS NOT NULL THEN [KeyStore].[Key] END ASC -- Offset paging OFFSET ((@Page - 1) * @PageSize) ROWS FETCH NEXT @PageSize ROWS ONLY |
0 
Spalteninformationen zu Prozeduren anzeigen
Der Profiler ist schon eine tolle Sache. Wenn man im SQL Management Studio eine Prozedur ausklappt, dann macht das Management Studio folgende Abfrage um die Informationen auszulesen:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
SELECT param.name AS [Name], 'Server[@Name=' + quotename(CAST( serverproperty(N'Servername') AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/StoredProcedure[@Name=' + quotename(sp.name,'''') + ' and @Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''') + ']' + '/Param[@Name=' + quotename(param.name,'''') + ']' AS [Urn], usrt.name AS [DataType], ISNULL(baset.name, N'') AS [SystemType], CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length], CAST(param.precision AS int) AS [NumericPrecision], CAST(param.scale AS int) AS [NumericScale], param.default_value AS [DefaultValue], param.is_output AS [IsOutputParameter], param.is_cursor_ref AS [IsCursorParameter], sp.object_id AS [IDText], db_name() AS [DatabaseName], param.name AS [ParamName], CAST( case when sp.is_ms_shipped = 1 then 1 when ( select major_id from sys.extended_properties where major_id = sp.object_id and minor_id = 0 and class = 1 and name = N'microsoft_database_tools_support') is not null then 1 else 0 end AS bit) AS [ParentSysObj], 1 AS [Number] FROM sys.all_objects AS sp INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = param.system_type_id) and (baset.user_type_id = param.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) |
Login