Back to OIM Explorer

dbo.QBM_FSQPrimaryKeyDef

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 2.481 characters

Interpretation

Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FGIPrimaryKeyName source text reference
references source dbo.QBM_FGIPrimaryKeyName_F source text reference

References

Referenced By

Complete Source

1CREATE FUNCTION dbo.QBM_FSQPrimaryKeyDef(2  @TableName nvarchar(64)3) RETURNS nvarchar(10244)5AS6BEGIN7  DECLARE @erg nvarchar(1024) = N ''8  DECLARE @val nvarchar(64)9  DECLARE @Indextype varchar(64) = NULL10  DECLARE @isMemoryOptimized BIT = 011  DECLARE @columnName1 varchar(30)12  DECLARE @columnName2 varchar(30)13  SELECT14    TOP 1 @Indextype = i.type_desc,15    @isMemoryOptimized = t.is_memory_optimized16  FROM sys.key_constraints c17    WITH(readpast)18  JOIN sys.tables t19    WITH(readpast)20    ON c.parent_object_id = t.object_id21  JOIN sys.indexes i22    WITH(readpast)23    ON c.name = i.name AND i.is_primary_key = 124  WHERE25    t.name = @TableName AND i.is_hypothetical = 0 AND i.type NOT IN(3,26  4,27  5,28  6)29  IF @Indextype IS NULL OR @Indextype = 'CLUSTERED'30  BEGIN31    SELECT @Indextype = ''32  END33  IF @isMemoryOptimized = 034  BEGIN35    SELECT36      @val = dbo.QBM_FGIPrimaryKeyName_F(@TableName,37      1)38    IF @val > ' '39    BEGIN40      SELECT @erg = N 'primary key ' + @Indextype + ' (' + @val41    END42    SELECT43      @val = dbo.QBM_FGIPrimaryKeyName_F(@TableName,44      2)45    IF @val > ' '46    BEGIN47      SELECT48        @erg = @erg + N ', ' + @val49    END50    IF @erg > ''51    BEGIN52      SELECT @erg = @erg + N ')'53    END54  END55  ELSE56  BEGIN57    SELECT TOP 1 @columnName1 = co.name58    FROM sys.key_constraints c59      WITH(readpast)60    JOIN sys.tables t61      WITH(readpast)62      ON c.parent_object_id = t.object_id63    JOIN sys.indexes i64      WITH(readpast)65      ON c.name = i.name AND i.is_primary_key = 1 AND i.is_hypothetical = 0 AND i.type NOT IN(3,66    4,67    5,68    6)69    JOIN sys.index_columns ic70      WITH(readpast)71      ON ic.object_id = t.object_id AND ic.index_id = i.index_id72    JOIN sys.columns co73      WITH(readpast)74      ON t.object_id = co.object_id AND co.column_id = ic.column_id75    WHERE76      t.name = @TableName77    ORDER BY ic.index_column_id78    IF @columnName1 > ' '79    BEGIN80      SELECT @erg = N 'primary key ' + @Indextype + ' (' + @columnName181    END82    SELECT TOP 1 @columnName2 = co.name83    FROM sys.key_constraints c84      WITH(readpast)85    JOIN sys.tables t86      WITH(readpast)87      ON c.parent_object_id = t.object_id88    JOIN sys.indexes i89      WITH(readpast)90      ON c.name = i.name AND i.is_primary_key = 1 AND i.is_hypothetical = 0 AND i.type NOT IN(3,91    4,92    5,93    6)94    JOIN sys.index_columns ic95      WITH(readpast)96      ON ic.object_id = t.object_id AND ic.index_id = i.index_id97    JOIN sys.columns co98      WITH(readpast)99      ON t.object_id = co.object_id AND co.column_id = ic.column_id100    WHERE101      t.name = @TableName AND co.name <> @columnName1102    ORDER BY ic.index_column_id DESC103    IF @columnName2 > ' '104    BEGIN105      SELECT106        @erg = @erg + N ', ' + @columnName2107    END108    IF @erg > ''109    BEGIN110      SELECT @erg = @erg + N ')'111    END112  END113  RETURN(@erg)114END

Open raw exported source

1    create   function dbo.QBM_FSQPrimaryKeyDef (@TableName nvarchar(64)) returns nvarchar(1024) as begin declare @erg nvarchar(1024) = N'' declare2 @val nvarchar(64) declare @Indextype varchar(64) = null declare @isMemoryOptimized bit = 0 declare @columnName1 varchar(30) declare @columnName2 varchar3(30) select top 1 @Indextype = i.type_desc , @isMemoryOptimized = t.is_memory_optimized from sys.key_constraints c with (readpast) join sys.tables t with4 (readpast) on c.parent_object_id = t.object_id join sys.indexes i with (readpast) on c.name = i.name and i.is_primary_key = 1 where t.name = @TableName5  and i.is_hypothetical = 0  and i.type not in (3,4,5,6) if @Indextype is null or @Indextype = 'CLUSTERED' begin select @Indextype = '' end if @isMemoryOptimized6 = 0 begin select @val = dbo.QBM_FGIPrimaryKeyName_F(@TableName , 1) if @val > ' ' begin select @erg = N'primary key ' + @Indextype + ' (' + @val end select7 @val = dbo.QBM_FGIPrimaryKeyName_F(@TableName , 2) if @val > ' ' begin select @erg = @erg + N', ' + @val end if @erg > '' begin select @erg = @erg + N')'8 end end else  begin  select top 1 @columnName1 = co.name from sys.key_constraints c with (readpast) join sys.tables t with (readpast) on c.parent_object_id9 = t.object_id join sys.indexes i with (readpast) on c.name = i.name  and i.is_primary_key = 1  and i.is_hypothetical = 0  and i.type not in (3,4,5,6) 10join sys.index_columns ic with (readpast) on ic.object_id = t.object_id and ic.index_id = i.index_id join sys.columns co with (readpast) on t.object_id11 = co.object_id and co.column_id = ic.column_id where t.name = @TableName order by ic.index_column_id if @columnName1 > ' ' begin select @erg = N'primary key '12 + @Indextype + ' (' + @columnName1 end select top 1 @columnName2 = co.name from sys.key_constraints c with (readpast) join sys.tables t with (readpast13) on c.parent_object_id = t.object_id join sys.indexes i with (readpast) on c.name = i.name  and i.is_primary_key = 1  and i.is_hypothetical = 0  and i.type14 not in (3,4,5,6) join sys.index_columns ic with (readpast) on ic.object_id = t.object_id and ic.index_id = i.index_id join sys.columns co with (readpast15) on t.object_id = co.object_id and co.column_id = ic.column_id where t.name = @TableName and co.name <> @columnName1  order by ic.index_column_id desc16 if @columnName2 > ' ' begin select @erg = @erg + N', ' + @columnName2 end if @erg > '' begin select @erg = @erg + N')' end end  return(@erg) end 17