Back to OIM Explorer

dbo.QBM_FGIPrimaryKeyName

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 2.152 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_FGIPrimaryKeyCount source text reference

References

Referenced By

Complete Source

SQL103 lines
1CREATE FUNCTION dbo.QBM_FGIPrimaryKeyName(2  @TableName varchar(30),3  @KeyNumber int = 14) RETURNS varchar(305)6AS7BEGIN8  DECLARE @erg varchar(30) = ''9  IF @TableName IN(10    SELECT TableName11    FROM QBM_VStartupTables)12  BEGIN13    IF @KeyNumber = 114    BEGIN15      SELECT TOP 1 @erg = Keyname116      FROM QBM_VStartupTables17      WHERE18        TableName = @TableName19      RETURN(@erg)20    END21    IF @KeyNumber = 222    BEGIN23      SELECT TOP 1 @erg = Keyname224      FROM QBM_VStartupTables25      WHERE26        TableName = @TableName27      RETURN(@erg)28    END29  END30  IF @KeyNumber = 131  BEGIN32    IF dbo.QBM_FGIPrimaryKeyCount(@TableName) = 133    BEGIN34      SELECT TOP 1 @erg = c.ColumnName35      FROM DialogColumn c36        WITH(readpast)37      JOIN DialogTable t38        WITH(readpast)39        ON c.UID_DialogTable = t.UID_DialogTable40      WHERE41        c.IsPKMember = 1 AND t.TableName = @TableName42    END43    ELSE44    BEGIN45      SELECT TOP 1 @erg = pk.COLUMN_NAME46      FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk47      JOIN sys.key_constraints kc48        ON kc.name = pk.CONSTRAINT_NAME AND kc.type = 'PK'49      WHERE50        pk.TABLE_NAME = @TableName51      ORDER BY pk.ORDINAL_POSITION ASC52      IF ISNULL(@erg,53      '') = ''54      BEGIN55        SELECT TOP 1 @erg = c.ColumnName56        FROM DialogColumn c57          WITH(readpast)58        JOIN DialogTable t59          WITH(readpast)60          ON c.UID_DialogTable = t.UID_DialogTable61        JOIN INFORMATION_SCHEMA.COLUMNS cc62          WITH(readpast)63          ON c.ColumnName = cc.COLUMN_NAME AND t.TableName = cc.TABLE_NAME64        WHERE65          c.IsPKMember = 1 AND t.TableName = @TableName66        ORDER BY cc.ORDINAL_POSITION ASC67      END68    END69    RETURN(@erg)70  END71  IF @KeyNumber = 272  BEGIN73    IF 2 = dbo.QBM_FGIPrimaryKeyCount(@TableName)74    BEGIN75      SELECT TOP 1 @erg = pk.COLUMN_NAME76      FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk77      JOIN sys.key_constraints kc78        ON kc.name = pk.CONSTRAINT_NAME AND kc.type = 'PK'79      WHERE80        pk.TABLE_NAME = @TableName81      ORDER BY pk.ORDINAL_POSITION DESC82      IF ISNULL(@erg,83      '') = ''84      BEGIN85        SELECT TOP 1 @erg = c.ColumnName86        FROM DialogColumn c87          WITH(readpast)88        JOIN DialogTable t89          WITH(readpast)90          ON c.UID_DialogTable = t.UID_DialogTable91        JOIN INFORMATION_SCHEMA.COLUMNS cc92          WITH(readpast)93          ON c.ColumnName = cc.COLUMN_NAME AND t.TableName = cc.TABLE_NAME94        WHERE95          c.IsPKMember = 1 AND t.TableName = @TableName96        ORDER BY cc.ORDINAL_POSITION DESC97      END98      RETURN(@erg)99    END100  END101  ende:102  RETURN(@erg)103END
Open raw exported source
SQL ยท Raw15 lines
1   create   function dbo.QBM_FGIPrimaryKeyName( @TableName varchar(30), @KeyNumber int = 1 ) returns varchar(30) as begin declare @erg varchar(302) = ''  if @TableName in (select TableName from QBM_VStartupTables) begin if @KeyNumber = 1 begin select top 1 @erg = Keyname1 from QBM_VStartupTables 3where TableName = @TableName  return(@erg)  end if @KeyNumber = 2 begin select top 1 @erg = Keyname2 from QBM_VStartupTables where TableName = @TableName4  return(@erg)  end end if @KeyNumber = 1 begin  if dbo.QBM_FGIPrimaryKeyCount(@TableName) = 1 begin select top 1 @erg = c.ColumnName from DialogColumn5 c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable where c.IsPKMember = 1 and t.TableName = @TableName end 6else begin select top 1 @erg = pk.COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk join sys.key_constraints kc on kc.name = pk.CONSTRAINT_NAME and7 kc.type = 'PK' where pk.TABLE_NAME = @TableName order by pk.ORDINAL_POSITION asc  if ISNULL(@erg, '') = '' begin select top 1 @erg = c.ColumnName from8 DialogColumn c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable  join INFORMATION_SCHEMA.COLUMNS cc with (readpast9) on c.ColumnName = cc.COLUMN_NAME and t.TableName = cc.TABLE_NAME where c.IsPKMember = 1 and t.TableName = @TableName order by cc.ORDINAL_POSITION asc10 end end  return(@erg)  end if @KeyNumber = 2 begin if 2 = dbo.QBM_FGIPrimaryKeyCount(@TableName) begin select top 1 @erg = pk.COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE11 pk join sys.key_constraints kc on kc.name = pk.CONSTRAINT_NAME and kc.type = 'PK' where pk.TABLE_NAME = @TableName order by pk.ORDINAL_POSITION desc  12if ISNULL(@erg, '') = '' begin select top 1 @erg = c.ColumnName from DialogColumn c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable13 = t.UID_DialogTable  join INFORMATION_SCHEMA.COLUMNS cc with (readpast) on c.ColumnName = cc.COLUMN_NAME and t.TableName = cc.TABLE_NAME where c.IsPKMember14 = 1 and t.TableName = @TableName order by cc.ORDINAL_POSITION desc end  return(@erg)  end end ende: return (@erg) end 15