Back to OIM Explorer

dbo.QBM_FTPrimaryKeyInfo_BT

Inline Table FunctionSQL_INLINE_TABLE_VALUED_FUNCTIONSandbox DB

Inline Table Function.

Source: sandbox-db sys.sql_modules

Source size: 2.042 characters

Interpretation

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

Relations

  • No extracted relations.

Typed Edges

  • No typed edges extracted for this source.

References

  • No direct source references extracted.

Referenced By

Complete Source

SQL71 lines
1CREATE FUNCTION dbo.QBM_FTPrimaryKeyInfo_BT(2) RETURNS TABLE3AS4RETURN(5SELECT6  e.TableName,7  e.ColumnName,8  e.UID_DialogColumn,9  e.UID_DialogTable,10  e.TableType,11  e.OrdinalPosition,12  sign(isnull(y.PK1Position, 0) + isnull(y.PK2Position, 0)) AS IsPKMember,13CASE y.PK1Position14  WHEN e.OrdinalPosition THEN15116ELSE 017END AS IsPKName1,18CASE y.PK2Position19  WHEN e.OrdinalPosition THEN20121ELSE 022END AS IsPKName2,23e.UID_DialogColumnUnionView24FROM(25SELECT26  t.TableName, c.ColumnName, c.UID_DialogColumn, t.UID_DialogTable, t.TableType, c.UID_DialogColumnUnionView,27  (columnproperty(sc.object_id, sc.name, 'ordinal')) AS OrdinalPosition, st.object_id AS Table_Object_id28FROM sys.tables st29  WITH(readpast)30JOIN sys.columns sc31  WITH(readpast)32  ON st.object_id = sc.object_id33JOIN DialogTable t34  WITH(readpast, forceseek)35  ON convert(varchar(30),36LEFT(st.name, 30)) = t.TableName AND t.TableType IN('B', 'T', 'M')37JOIN DialogColumn c38  WITH(readpast, forceseek)39  ON t.UID_DialogTable = c.UID_DialogTable AND convert(varchar(30),40LEFT(sc.name, 30)) = c.ColumnName) AS e41LEFT42OUTER43JOIN(44SELECT45  min(x.ORDINAL_POSITION) AS PK1Position, CASE46  WHEN max(x.ORDINAL_POSITION) = min(x.ORDINAL_POSITION) THEN47  048ELSE max(x.ORDINAL_POSITION)49END AS PK2Position, x.object_id50FROM(51SELECT52  w.COLUMN_NAME, w.ORDINAL_POSITION, w.object_id53FROM(54SELECT55  c.name AS COLUMN_NAME,(columnproperty(t.object_id, c.name, 'ordinal')) AS ORDINAL_POSITION, t.object_id56FROM sys.tables t57  WITH(readpast)58JOIN sys.indexes i59  WITH(readpast)60  ON t.object_id = i.object_id AND i.is_hypothetical = 0 AND i.type NOT IN(3, 4, 5, 6)61JOIN sys.index_columns ic62  WITH(readpast)63  ON i.index_id = ic.index_id AND ic.object_id = t.object_id64JOIN sys.columns c65  WITH(readpast)66  ON c.object_id = t.object_id AND ic.column_id = c.column_id67WHERE68  i.is_primary_key = 1) AS w69GROUP BY w.object_id, w.COLUMN_NAME, w.ORDINAL_POSITION) AS x70GROUP BY x.object_id) AS y71  ON e.Table_Object_id = y.object_id AND(e.OrdinalPosition IN(y.PK1Position, y.PK2Position)))
Open raw exported source
SQL ยท Raw15 lines
1create function dbo.QBM_FTPrimaryKeyInfo_BT( ) returns table as return ( select e.TableName , e.ColumnName , e.UID_DialogColumn , e.UID_DialogTable , e.TableType2 , e.OrdinalPosition , sign(isnull(y.PK1Position, 0) + isnull(y.PK2Position, 0)) as IsPKMember , case y.PK1Position when e.OrdinalPosition then 1 else 30 end as IsPKName1 , case y.PK2Position when e.OrdinalPosition then 1 else 0 end as IsPKName2 , e.UID_DialogColumnUnionView  from ( select t.TableName,4 c.ColumnName, c.UID_DialogColumn, t.UID_DialogTable, t.TableType , c.UID_DialogColumnUnionView , ( columnproperty(sc.object_id, sc.name, 'ordinal') ) 5as OrdinalPosition , st.object_id as Table_Object_id from sys.tables st with (readpast) join sys.columns sc with (readpast) on st.object_id = sc.object_id6   join DialogTable t with (readpast, forceseek) on convert(varchar(30), left(st.name,30)) = t.TableName and t.TableType in('B', 'T', 'M') join DialogColumn7 c with (readpast, forceseek) on t.UID_DialogTable = c.UID_DialogTable  and convert(varchar(30), left(sc.name,30)) = c.ColumnName ) as e left outer join8( select min(x.ORDINAL_POSITION) as PK1Position , case when max(x.ORDINAL_POSITION) = min(x.ORDINAL_POSITION) then 0 else max(x.ORDINAL_POSITION) end as9 PK2Position , x.object_id from( select w.COLUMN_NAME , w.ORDINAL_POSITION , w.object_id from( select c.name as COLUMN_NAME , ( columnproperty(t.object_id10, c.name, 'ordinal') ) as ORDINAL_POSITION , t.object_id from sys.tables t with (readpast) join sys.indexes i with (readpast) on t.object_id = i.object_id11  and i.is_hypothetical = 0  and i.type not in (3,4,5,6) join sys.index_columns ic with (readpast) on i.index_id = ic.index_id and ic.object_id = t.object_id12 join sys.columns c with (readpast) on c.object_id = t.object_id and ic.column_id = c.column_id where i.is_primary_key = 1 ) as w group by w.object_id 13, w.COLUMN_NAME , w.ORDINAL_POSITION ) as x group by x.object_id ) as y on e.Table_Object_id = y.object_id and ( e.OrdinalPosition in ( y.PK1Position, 14y.PK2Position) ) ) 15