Thursday, March 22, 2012

Find Database Table's Foreign key mappings in SQL Server

The Foreign key mapping is stored in system database in SQL server, here is the query to find all the mappings.

Use <Database Name>
GO

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id



Find Database Table's Foreign key mappings in SQL Server

The Foreign key mapping is stored in system database in SQL server, here is the query to find all the mappings.

Use <Database Name>
GO

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id



Get Random string function for SQL

Most of the time we need a function in sql server to add random string in any field.

So I have created a function, which accepts the guid and generates the random string form that.



IF OBJECT_ID (N'dbo.get_RandomChar', N'FN') IS NOT NULL
    DROP FUNCTION dbo.get_RandomChar;
GO
-- Function to generate the random char, if the char is less then A then use A and if it is greater then z then use Z.
CREATE FUNCTION dbo.get_RandomChar(@newguid varchar(255))
RETURNS varChar(10)
WITH EXECUTE AS CALLER
AS
BEGIN
    declare @NewStr varchar(25)
set @NewStr =''
declare @Randomint int 
set @Randomint =ABS(CAST(@newguid AS binary(10)) %100)
-- not in A to Z and a to Z  _ - space and *
if not((@Randomint >= 65 and @Randomint <=90 ) or (@Randomint >= 97 and @Randomint <=122 ) or @Randomint=95 or @Randomint=45 or @Randomint =32 or @Randomint =42)
Begin
if (@Randomint <65)
set @Randomint =65
else
set @Randomint =122
End
set @NewStr =@NewStr+ convert(varchar,CHAR(@Randomint))
return(@NewStr);
END;
GO

-- function to create random string of 10 chars.
IF OBJECT_ID (N'dbo.get_RandomString', N'FN') IS NOT NULL
    DROP FUNCTION dbo.get_RandomString;
GO
CREATE FUNCTION dbo.get_RandomString(@newguid1 varchar(255),@newguid2 varchar(255),@newguid3 varchar(255),@newguid4 varchar(255),@newguid5 varchar(255),@newguid6 varchar(255),@newguid7 varchar(255),@newguid8 varchar(255),@newguid9 varchar(255),@newguid10 varchar(255))
RETURNS varChar(10)
WITH EXECUTE AS CALLER
AS
BEGIN
    return(dbo.get_RandomChar(@newguid1) +dbo.get_RandomChar(@newguid2)+dbo.get_RandomChar(@newguid3)+dbo.get_RandomChar(@newguid4)+dbo.get_RandomChar(@newguid5)+dbo.get_RandomChar(@newguid6)+dbo.get_RandomChar(@newguid7)+dbo.get_RandomChar(@newguid8)+dbo.get_RandomChar(@newguid9) +dbo.get_RandomChar(@newguid10));
END;
GO





to use this function call  like get_RandomString(NEWID(),NEWID(),NEWID(),NEWID(),NEWID(),NEWID(),NEWID(),NEWID(),NEWID(),NEWID()) 

most of the time when we do data scramble we need such function.




Get Random string function for SQL

Most of the time we need a function in sql server to add random string in any field.

So I have created a function, which accepts the guid and generates the random string form that.



IF OBJECT_ID (N'dbo.get_RandomChar', N'FN') IS NOT NULL
    DROP FUNCTION dbo.get_RandomChar;
GO
-- Function to generate the random char, if the char is less then A then use A and if it is greater then z then use Z.
CREATE FUNCTION dbo.get_RandomChar(@newguid varchar(255))
RETURNS varChar(10)
WITH EXECUTE AS CALLER
AS
BEGIN
    declare @NewStr varchar(25)
set @NewStr =''
declare @Randomint int 
set @Randomint =ABS(CAST(@newguid AS binary(10)) %100)
-- not in A to Z and a to Z  _ - space and *
if not((@Randomint >= 65 and @Randomint <=90 ) or (@Randomint >= 97 and @Randomint <=122 ) or @Randomint=95 or @Randomint=45 or @Randomint =32 or @Randomint =42)
Begin
if (@Randomint <65)
set @Randomint =65
else
set @Randomint =122
End
set @NewStr =@NewStr+ convert(varchar,CHAR(@Randomint))
return(@NewStr);
END;
GO

-- function to create random string of 10 chars.
IF OBJECT_ID (N'dbo.get_RandomString', N'FN') IS NOT NULL
    DROP FUNCTION dbo.get_RandomString;
GO
CREATE FUNCTION dbo.get_RandomString(@newguid1 varchar(255),@newguid2 varchar(255),@newguid3 varchar(255),@newguid4 varchar(255),@newguid5 varchar(255),@newguid6 varchar(255),@newguid7 varchar(255),@newguid8 varchar(255),@newguid9 varchar(255),@newguid10 varchar(255))
RETURNS varChar(10)
WITH EXECUTE AS CALLER
AS
BEGIN
    return(dbo.get_RandomChar(@newguid1) +dbo.get_RandomChar(@newguid2)+dbo.get_RandomChar(@newguid3)+dbo.get_RandomChar(@newguid4)+dbo.get_RandomChar(@newguid5)+dbo.get_RandomChar(@newguid6)+dbo.get_RandomChar(@newguid7)+dbo.get_RandomChar(@newguid8)+dbo.get_RandomChar(@newguid9) +dbo.get_RandomChar(@newguid10));
END;
GO





to use this function call  like get_RandomString(NEWID(),NEWID(),NEWID(),NEWID(),NEWID(),NEWID(),NEWID(),NEWID(),NEWID(),NEWID()) 

most of the time when we do data scramble we need such function.