Back to OIM Explorer

dbo.QBM_FSQPrimaryKeyDef_S

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 4.134 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_FSQPrimaryKeyDef source text reference

References

Referenced By

Complete Source

SQL172 lines
1CREATE FUNCTION dbo.QBM_FSQPrimaryKeyDef_S(2  @TableOrTypeName nvarchar(64)3) RETURNS nvarchar(10244)5AS6BEGIN7  DECLARE @erg nvarchar(1024) = N ''8  DECLARE @Indextype varchar(64) = NULL9  DECLARE @columnName1 varchar(30)10  DECLARE @columnName2 varchar(30)11  DECLARE @bucketCount int12  IF EXISTS(13    SELECT TOP 1 114    FROM sys.tables t15    WITH(readpast)16  WHERE17    t.name = @TableOrTypeName)18  BEGIN19    SELECT20      TOP 1 @Indextype = i.type_desc,21      @bucketCount = h.bucket_count22    FROM sys.key_constraints c23      WITH(readpast)24    JOIN sys.tables t25      WITH(readpast)26      ON c.parent_object_id = t.object_id27    JOIN sys.indexes i28      WITH(readpast)29      ON c.name = i.name AND i.is_primary_key = 1 AND i.is_hypothetical = 0 AND i.type NOT IN(3,30    4,31    5,32    6)33    LEFT34    OUTER35    JOIN sys.hash_indexes h36      WITH(readpast)37      ON h.index_id = i.index_id AND h.object_id = i.object_id38    WHERE39      t.name = @TableOrTypeName40    SELECT TOP 1 @columnName1 = co.name41    FROM sys.key_constraints c42      WITH(readpast)43    JOIN sys.tables t44      WITH(readpast)45      ON c.parent_object_id = t.object_id46    JOIN sys.indexes i47      WITH(readpast)48      ON c.name = i.name AND i.is_primary_key = 1 AND i.is_hypothetical = 0 AND i.type NOT IN(3,49    4,50    5,51    6)52    JOIN sys.index_columns ic53      WITH(readpast)54      ON ic.object_id = t.object_id AND ic.index_id = i.index_id55    JOIN sys.columns co56      WITH(readpast)57      ON t.object_id = co.object_id AND co.column_id = ic.column_id58    WHERE59      t.name = @TableOrTypeName60    ORDER BY ic.index_column_id61    SELECT TOP 1 @columnName2 = co.name62    FROM sys.key_constraints c63      WITH(readpast)64    JOIN sys.tables t65      WITH(readpast)66      ON c.parent_object_id = t.object_id67    JOIN sys.indexes i68      WITH(readpast)69      ON c.name = i.name AND i.is_primary_key = 1 AND i.is_hypothetical = 0 AND i.type NOT IN(3,70    4,71    5,72    6)73    JOIN sys.index_columns ic74      WITH(readpast)75      ON ic.object_id = t.object_id AND ic.index_id = i.index_id76    JOIN sys.columns co77      WITH(readpast)78      ON t.object_id = co.object_id AND co.column_id = ic.column_id79    WHERE80      t.name = @TableOrTypeName AND co.name <> @columnName181    ORDER BY ic.index_column_id DESC82  END83  ELSE84  BEGIN85    SELECT86      TOP 1 @Indextype = i.type_desc,87      @bucketCount = h.bucket_count88    FROM sys.key_constraints c89      WITH(readpast)90    JOIN sys.table_types t91      WITH(readpast)92      ON c.parent_object_id = t.type_table_object_id93    JOIN sys.indexes i94      WITH(readpast)95      ON c.name = i.name AND i.is_primary_key = 1 AND i.is_hypothetical = 0 AND i.type NOT IN(3,96    4,97    5,98    6)99    LEFT100    OUTER101    JOIN sys.hash_indexes h102      WITH(readpast)103      ON h.index_id = i.index_id AND h.object_id = i.object_id104    WHERE105      t.name = @TableOrTypeName106    SELECT TOP 1 @columnName1 = co.name107    FROM sys.key_constraints c108      WITH(readpast)109    JOIN sys.table_types t110      WITH(readpast)111      ON c.parent_object_id = t.type_table_object_id112    JOIN sys.indexes i113      WITH(readpast)114      ON c.name = i.name AND i.is_primary_key = 1 AND i.is_hypothetical = 0 AND i.type NOT IN(3,115    4,116    5,117    6)118    JOIN sys.index_columns ic119      WITH(readpast)120      ON ic.object_id = t.type_table_object_id AND ic.index_id = i.index_id121    JOIN sys.columns co122      WITH(readpast)123      ON co.object_id = t.type_table_object_id AND co.column_id = ic.column_id124    WHERE125      t.name = @TableOrTypeName126    ORDER BY ic.index_column_id127    SELECT TOP 1 @columnName2 = co.name128    FROM sys.key_constraints c129      WITH(readpast)130    JOIN sys.table_types t131      WITH(readpast)132      ON c.parent_object_id = t.type_table_object_id133    JOIN sys.indexes i134      WITH(readpast)135      ON c.name = i.name AND i.is_primary_key = 1 AND i.is_hypothetical = 0 AND i.type NOT IN(3,136    4,137    5,138    6)139    JOIN sys.index_columns ic140      WITH(readpast)141      ON ic.object_id = t.type_table_object_id AND ic.index_id = i.index_id142    JOIN sys.columns co143      WITH(readpast)144      ON co.object_id = t.type_table_object_id AND co.column_id = ic.column_id145    WHERE146      t.name = @TableOrTypeName AND co.name <> @columnName1147    ORDER BY ic.index_column_id DESC148  END149  IF @columnName1 > ' '150  BEGIN151    SELECT152      @erg = N ', primary key ' + @Indextype + ' (' + @columnName1153  END154  IF @Indextype IS NULL OR @Indextype = 'CLUSTERED'155  BEGIN156    SELECT @Indextype = ''157  END158  IF @columnName2 > ' '159  BEGIN160    SELECT161      @erg = @erg + N ', ' + @columnName2162  END163  IF @erg > ''164  BEGIN165    SELECT @erg = @erg + N ')'166  END167  IF @bucketCount > 0168  BEGIN169    SELECT @erg = @erg + ' with (BUCKET_COUNT = ' + ltrim(STR(@bucketCount)) + ')'170  END171  RETURN(@erg)172END
Open raw exported source
SQL ยท Raw28 lines
1   create   function dbo.QBM_FSQPrimaryKeyDef_S (@TableOrTypeName nvarchar(64)) returns nvarchar(1024)  as begin declare @erg nvarchar(1024) = N''2  declare @Indextype varchar(64) = null declare @columnName1 varchar(30) declare @columnName2 varchar(30) declare @bucketCount int if exists (select top3 1 1 from sys.tables t with (readpast) where t.name = @TableOrTypeName ) begin select top 1 @Indextype = i.type_desc , @bucketCount = h.bucket_count from4 sys.key_constraints c with (readpast) join sys.tables t with (readpast) on c.parent_object_id = t.object_id join sys.indexes i with (readpast) on c.name5 = i.name and i.is_primary_key = 1  and i.is_hypothetical = 0  and i.type not in (3,4,5,6) left outer join sys.hash_indexes h with (readpast) on h.index_id6 = i.index_id and h.object_id = i.object_id where t.name = @TableOrTypeName  select top 1 @columnName1 = co.name from sys.key_constraints c with (readpast7) join sys.tables t with (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 = 18  and i.is_hypothetical = 0  and i.type 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_id9 join sys.columns co with (readpast) on t.object_id = co.object_id and co.column_id = ic.column_id where t.name = @TableOrTypeName order by ic.index_column_id10 select top 1 @columnName2 = co.name from sys.key_constraints c with (readpast) join sys.tables t with (readpast) on c.parent_object_id = t.object_id join11 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) join sys.index_columns12 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_id = co.object_id and co.column_id13 = ic.column_id where t.name = @TableOrTypeName and co.name <> @columnName1  order by ic.index_column_id desc end else begin  select top 1 @Indextype =14 i.type_desc , @bucketCount = h.bucket_count from sys.key_constraints c with (readpast) join sys.table_types t with (readpast) on c.parent_object_id = 15t.type_table_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,416,5,6) left outer join sys.hash_indexes h with (readpast) on h.index_id = i.index_id and h.object_id = i.object_id where t.name = @TableOrTypeName  select17 top 1 @columnName1 = co.name from sys.key_constraints c with (readpast) join sys.table_types t with (readpast) on c.parent_object_id = t.type_table_object_id18 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) join sys.index_columns19 ic with (readpast) on ic.object_id = t.type_table_object_id and ic.index_id = i.index_id join sys.columns co with (readpast) on co.object_id = t.type_table_object_id20 and co.column_id = ic.column_id where t.name = @TableOrTypeName order by ic.index_column_id select top 1 @columnName2 = co.name from sys.key_constraints21 c with (readpast) join sys.table_types t with (readpast) on c.parent_object_id = t.type_table_object_id join sys.indexes i with (readpast) on c.name =22 i.name  and i.is_primary_key = 1  and i.is_hypothetical = 0  and i.type not in (3,4,5,6) join sys.index_columns ic with (readpast) on ic.object_id = t.type_table_object_id23 and ic.index_id = i.index_id join sys.columns co with (readpast) on co.object_id = t.type_table_object_id and co.column_id = ic.column_id where t.name24 = @TableOrTypeName and co.name <> @columnName1  order by ic.index_column_id desc end if @columnName1 > ' ' begin select @erg = N', primary key ' + @Indextype25 + ' (' + @columnName1 end if @Indextype is null or @Indextype = 'CLUSTERED' begin select @Indextype = '' end if @columnName2 > ' ' begin select @erg =26 @erg + N', ' + @columnName2 end if @erg > '' begin select @erg = @erg + N')' end if @bucketCount > 0 begin select @erg = @erg + ' with (BUCKET_COUNT = '27+ ltrim(STR(@bucketCount)) +')' end return(@erg) end 28