SQL Script to drop and re-create all foreign keys in SQL server

I had to load some database with fresh data every few days for testing purpose and some reporting purpose. I have a SSIS package to load the data from source database but one issue I faced is I could not truncate the tables because of foreign keys. So Initially I started with using DELETE, but which was taking some time. So to improve the performance and get rid of all foreign key before start of the load and then re-create them after load is finish, I have written one interesting script as below. This script give DROP and CREATE statement for all foreign key which I was running in my package.

WITH RefColumns AS

(

       SELECT

              C.referenced_object_id AS [object_id],

              C.parent_object_id,

              STUFF((SELECT ', ' + QUOTENAME(B.name)

                     FROM sys.foreign_key_columns A 

                           JOIN sys.columns B ON B.[object_id] = A.referenced_object_id AND B.column_id = A.referenced_column_id

                           WHERE C.parent_object_id = A.parent_object_id AND C.referenced_object_id = A.referenced_object_id

                           FOR XML PATH('')), 1, 2, '') AS ColumnNames

       FROM sys.foreign_key_columns C

       GROUP BY C.referenced_object_id, C.parent_object_id

)

,ParentColumns AS

(

       SELECT

              C.parent_object_id AS [object_id],

              C.referenced_object_id,

              STUFF((SELECT ', ' + QUOTENAME(B.name)

                     FROM sys.foreign_key_columns A 

                           JOIN sys.columns B ON B.[object_id] = A.parent_object_id AND B.column_id = A.parent_column_id

                           WHERE C.parent_object_id = A.parent_object_id AND C.referenced_object_id = A.referenced_object_id

                           FOR XML PATH('')), 1, 2, '') AS ColumnNames

       FROM sys.foreign_key_columns C

       GROUP BY C.parent_object_id, C.referenced_object_id

)

 

SELECT

       'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' + QUOTENAME(PT.name) + ' DROP  CONSTRAINT' + ' ' + QUOTENAME(FK.name) AS [DropFKScript],

       'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' + QUOTENAME(PT.name) + ' WITH CHECK ADD  CONSTRAINT '+ QUOTENAME(FK.name) + CHAR(13) + CHAR(10) +

       'FOREIGN KEY(' + PC.ColumnNames + ')' + CHAR(13) + CHAR(10) +

       'REFERENCES ' + QUOTENAME(SCHEMA_NAME(RT.[schema_id])) + '.' + QUOTENAME(RT.name) + ' (' + RC.ColumnNames + ')' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +

       'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' + QUOTENAME(PT.name) + ' CHECK CONSTRAINT ' + QUOTENAME(FK.name) + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)

       AS [CreateFKScript]

FROM sys.foreign_keys FK   

       JOIN sys.tables PT ON PT.[object_id] = FK.parent_object_id

       JOIN ParentColumns AS PC ON PC.[object_id] = FK.parent_object_id AND PC.referenced_object_id = FK.referenced_object_id

       JOIN sys.tables RT ON RT.[object_id] = FK.referenced_object_id

       JOIN RefColumns AS RC ON RC.[object_id] = FK.referenced_object_id AND RC.parent_object_id = FK.parent_object_id

WHERE PT.name NOT IN ('dtproperties', 'sysdiagrams', '__RefactorLog')

       AND RT.name NOT IN ('dtproperties', 'sysdiagrams', '__RefactorLog')

ORDER BY PT.name

GO

 

If anyone find any error or think this can be improved then please do post your comment.

Compare data size definition and actual data for string field

Most of the time we import data from file with default field length of 255. But once it is imported you might want to set it to correct size because of many reasons such as

• For Constraint and keys. 
• Indexing strategy implementation.
• Improve string comparison.
• Maintaining data base field sizing standards 

and more many other reason.

To do this we have to get the max length of current data and the compare it with database definition. We might have to do this for any existing table.

I have written one script which give the comparison of field size defined in table and actual data size. You can use this comparison  and write script to make the necessary correction.

You can also use this script with some modification to generate ALTER script if you have defined rule for field size.

DECLARE @query VARCHAR(MAX)

DECLARE @tableName VARCHAR(255) = '[dbo].[SomeTable]'

SELECT @query = 'SELECT ' + SUBSTRING(A, 4, LEN(A)) + ' FROM ' + @tableName

FROM

       (

              SELECT

                     (

                     SELECT ' , MAX(LEN(['+ name +'])) AS ['+ name +']'

                     FROM sys.columns

                     WHERE [object_id] = OBJECT_ID(@tableName)

                           AND TYPE_NAME(system_type_id) IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR')

                     FOR XML PATH ('')

                     ) AS A

       ) AS B

SELECT @query = ' SELECT A.ColumnName, UPPER(TYPE_NAME(B.system_type_id)) AS DataType, CASE WHEN TYPE_NAME(B.system_type_id) IN (''NCHAR'', ''NVARCHAR'') THEN B.max_length/2 ELSE B.max_length END AS MaxSizeByDefinition, A.MaxSizeByActualData

--Add here your script for ALTER query

       FROM ( ' + @query + ' ) AS SourceTable UNPIVOT (MaxSizeByActualData FOR ColumnName IN ( ' + SUBSTRING(A, 4, LEN(A)) + ' )) AS A

       INNER JOIN sys.columns AS B ON B.name = A.ColumnName AND B.[object_id] = OBJECT_ID(''' + @tableName + ''') ORDER BY B.column_id'

FROM

       (

              SELECT

                     (

                     SELECT ' , ['+ name + ']' FROM sys.columns C WHERE [object_id] = OBJECT_ID(@tableName) AND TYPE_NAME(system_type_id) IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR') FOR XML PATH ('')

                     ) AS A

       ) AS B

EXECUTE(@query)

Tool to view big data file

I have to load big data files using SSIS or SQL server Import/Export wizard. Most of the time it works fine but if it fails and I have to view the data to debug then it is simply impossible to view the data because most of the text editor cannot open file which too big (size in multiple of GBs). So  I have developed small tool which can help to read or view a big file which cannot be opened in normal text editor.  This tool can also be use full if you are developing SSIS package to load big file and want to create small sample file from big file during development.

Click here to download the tool.

Features of the tools

 - Load big file (no limit on size)
 - View data page by page. Page size is configurable.
 - Save page in different file.
 - Go to any page in file.
 - Displays line number across  pages.

Limitations.
 - It uses Temp directory to store data pages while opening file. So virtually it can occupy similar disk space as the original file.
- It can only read file line by line. So if lines are not separated by {CR}{LF} then it cannot do data paging.
- It can only work with ANSI data.


Read big data file | View big data file | Open big data file | tool to open big file | tool to read big data file | Preview big data file | Get sample data from big data file | Read specific data from big file | View specific data from big file

Hello World

Since very long I was thinking to write my blog but I could not start writing probably because of my laziness. Finally today I took a wise decision to write my own blog. I am not sure how long I will continue writing but now I will try to write something on regular basis. If you have come this blog then please do write comment, so that I will motivation to continue writing. You might be thinking that why only today I took this decision. Even I don't know why. So like me don't think and enjoy(!!!) reading my blog.


I think I said enough about reason for starting blog. Now let me tell you what I am gonna write in my blog. From the name (SQL31) you might have guessed that it is something about SQL. You are right, I have some knowledge about SQL which I want to share here. If it is about SQL then what is 31? There is no magic about it. It is my birth day. I was typing some name for my blog and could not find good word so I said let me try this and it was available. That's it. It is SQL31. Good name! Isn't it?


No let me tell you about my self. I am a SQL Server DBA/Developer since 2003. I have worked on SQL server 2000, 2005, 2008, 2008R2, DTS, SSIS, SSAS, SSRS. Apart from SQL server I also have experience of .Net, C#, VB.NET, ASP.Net, ASP MVC, Silverlight, WPF, WCF, XML, HTML, DHTML, Javascript.


That's all for this post. Enjoy the following posts.