Finding all the foreign key references in a database

Hi All,
 Below query we can used to find all the foreign key references in a database.

SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
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
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
GO

Count Difference of Tables in Two Databases

SET NOCOUNT ON;
DECLARE @name VARCHAR(50)
DECLARE @DBName1 VARCHAR(50)
DECLARE @DBName2 VARCHAR(50)
DECLARE @QName1 VARCHAR(250)
DECLARE @QName2 VARCHAR(MAX)
Create Table #temp(Dbname varchar(50),tablename varchar(50), Count1 int, Count2 varchar(50))
SET @DBName1= 'DataBase Name 1'
SET @QName1= @DBName1 +'.sys.tables'
SET @QName2 ='Insert into #temp(Dbname,tablename) '+  'Select ' +'''' +@DBName1+''', name from '+  @QName1 + ' order by 1'
--PRINT @QName2
EXEC (@QName2)

Create Table #temp1(Dbname varchar(50),tablename varchar(50))
SET @DBName2='DataBase Name 2'
SET @QName1= @DBName2 +'.sys.tables'
SET @QName2 ='Insert into #temp1(Dbname,tablename) '+  'Select ' +'''' +@DBName2+''', name from '+  @QName1 + ' order by 1'
--PRINT @QName2
EXEC (@QName2)

UPDATE #temp
SET Count2 = 'Not present'
Where tablename IN(Select A.tablename FROM #temp A LEFT OUTER JOIN #temp1 B
ON A.tablename = B.tablename WHERE B.tablename is null)

DECLARE db_cursor CURSOR FOR 
  Select tablename from #temp
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0 
BEGIN 
      SET @QName1 = 'Update  #temp SET Count1 =( Select COUNT(*) FROM ' +@DBName1+'.dbo.' + @name +
                     ') where Tablename= '''+@name +''''
      --PRINT @QName1
      SET @QName2 = 'IF EXISTS (SELECT name FROM ' +@DBName2 +'.sys.tables where name='''+ @name +
                    ''')Update  #temp SET Count2 =(Select COUNT(*) FROM ' +@DBName2+'.dbo.' + @name +
                     ') where Tablename= '''+@name +''''
      --PRINT @QName2
      EXEC (@QName1)
      EXEC (@QName2)
      FETCH NEXT FROM db_cursor INTO @name 
END 
Select tablename,Count1 As CountInSourceTable, Count2 AS CountInDestTable from #temp order by 1
CLOSE db_cursor 
DEALLOCATE db_cursor
Drop TABLE #temp
Drop TABLE #temp1
SET NOCOUNT OFF;

Searching All Objects Which Uses A Particular Column

1) Search in Stored Procedure


SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'Name Your Column Here' + '%'
AND TYPE = 'P'

2) Search in All Objects

This script search stored procedures, views, functions as well other objects.

-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' +'Name Your Column Here' + '%'
GO

3) Search in Stored Procedure

This script search only stored procedure for specified column.

-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'Name Your Column Here' + '%'

Full Text Indexing

How to make Full Text Indexing Easier in a Database of large number of tables

Full-text search refers to the functionality in SQL Server that supports queries against character-based data. These types of queries can include words and phrases as well as multiple forms of a word or phrase. A full-text index is made up of collection of words that are derived from the text being indexed. So, for implementing a full text index, we have to create a catalog first and then this catalog should be mapped with tables and corresponding columns to be indexed in the database.

The 3 sections to create full text indexing. they are,

·         Create a Full Text Catalog

·         Create Full Text Index for the columns in different Tables

·         Populate the Index

Step 1: Select the database. Under Storage select ‘Full Text Catalogs’. Create a New Full Text Catalog  

            

Step 2: Give a name for the catalog and click OK.

You can see the catalog under Full Text Catalogs in storage.

Step 3: You can create the index in 2 ways. I am explaining both here.

Method 1

  Right Click on the catalog created and select properties.

 

Select the Tables/View from the left pane.

         (A)   Select the table name to which you want to create and put it in the right box. Here we have selected the table ‘tbl_company’ as table to be indexed.

          (B)   Select the unique index

          (C)   Select the column to be indexed from available columns. Here we have selected the ‘company_name’ column to be indexed.

Then click OK.

Method 2

Right Click the Table to be indexed. Select Full Text Index and the again select ‘Define Full Text Index’

It will display a Full Text Indexing Wizard. Click on Next Button. Select the unique index.

Select the columns to which you want indexing

Select the catalog created in the first step. If you want to create a separate catalog, you can create the same by checking ‘Create a New Catalog’.

Step 4: Right click on the table. Select Full Text and again select Start Population

Contains & FreeText

Contains:  It can search for a word or phrase

        (1)     Exact Match for a word or phrase. Use double quotes

                 Select * from table_name

                 where contains(search_content,'"John"')

     

     (2)    Searching for the word ‘Application’

Select * from tbl_search_index where contains(search_content,'Application')

       

    (3)    FreeText

Select * from tbl_search_index where freetext(search_content,'Application')

Advantages

·         Faster Search

·         Character Based Search

·         Boolean searches

·         More relevant results

Conclusion

Full Text Search is a powerful and useful feature. It is easy to implement and gives users the ability to quickly search through huge amount of data.