|
|
patrick gallucci's sql server brain drain
-
This is a follow-up to the blog entry from Jasper Jugan. This is a modification to the script to allow for schema specific deletes of the SP's, Views and I added Functions.
create procedure usp_DropSPFunctionsViews
as
-- variable to object name
declare @name varchar(1000)
-- variable to hold object type
declare @xtype varchar(20)
-- variable to hold sql string
declare @sqlstring nvarchar(4000)
declare SPViews_cursor cursor for
SELECT QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME) AS name, ROUTINE_TYPE AS xtype
FROM
INFORMATION_SCHEMA.ROUTINES
UNION
SELECT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) AS name, 'VIEW' AS xtype
FROM
INFORMATION_SCHEMA.VIEWS
open SPViews_cursor
fetch next from SPViews_cursor into @name, @xtype
while @@fetch_status = 0
begin
-- test object type if it is a stored procedure
if @xtype = 'PROCEDURE'
begin
set @sqlstring = 'drop procedure ' + @name
exec sp_executesql @sqlstring
set @sqlstring = ' '
end
-- test object type if it is a function
if @xtype = 'FUNCTION'
begin
set @sqlstring = 'drop FUNCTION ' + @name
exec sp_executesql @sqlstring
set @sqlstring = ' '
end
-- test object type if it is a view
if @xtype = 'VIEW'
begin
set @sqlstring = 'drop view ' + @name
exec sp_executesql @sqlstring
set @sqlstring = ' '
end
-- get next record
fetch next from SPViews_cursor into @name, @xtype
end
close SPViews_cursor
deallocate SPViews_cursor
GO
|
-
And I really do mean fortunate. On March 10th I am to embark in five weeks of some of the toughest technical training that I will do during my career. I have been to week long ramp-ups and boot-camps to help facilitate learning during my career. But this is far and above anything that I think I endured or will ever go through again. This is not training to teach, but rather to enforce and to fill gaps. You must be nominated, then pass a pre-interview to make sure that you are qualified to attempt the certification. I am finishing up the last pre-requisites this week and my management has allowed me to clear my calendar for five weeks as well as foot the cost of my training at our Redmond Campus - not cheap. Stay tuned! If you would like more information on the MCA program please go to the Microsoft Learning Site. 
|
-
-
I use this when I need to join two fields. One has a date, the other has a time. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_MergeDate2Time]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [util].[uf_MergeDate2Time]
GO
CREATE FUNCTION [util].[uf_MergeDate2Time](
@date DATETIME,
@time DATETIME
)
RETURNS DATETIME
WITH EXECUTE AS CALLER
AS
/**********************************************************************************************************
* UDF Name:
* [util].[uf_MergeDate2Time]
* Parameters:
* @date datetime - The date to merge
@time datetime - The time to merge
* Purpose: This function returns a datetime of the @date variable concatendated to the @time variable.
*
* Example:
select [util].[uf_MergeDate2Time]('12/25/2007', GETDATE())
*
* Revision Date/Time:
* November 1, 2007
*
**********************************************************************************************************/
BEGIN
-- declare variables
DECLARE @result datetime;
-- determine half year date
SET @result = CAST(LEFT(CONVERT(NVARCHAR(40), @date, 121), 10) + ' ' + RIGHT(CONVERT(NVARCHAR(40), @time, 121), 12) AS DATETIME)
RETURN @result;
END;
GO
SELECT [util].[uf_MergeDate2Time]('2007-12-25', GETDATE());
GO
|
-
So here is my 2nd favorite new gadget on my Windows Mobile device. 
With the 3G network speed and my SlingPlayer mobile, when I am not using Mobile Live search, I am either watching tv or listening to the news on my Windows Mobile Device. The quality is not great, but it is acceptable. I have been trying different settings, and over the past 2 weeks have gotten it to where there is little fragmentation on the screen. The more action on the screen lessens the quality, but the player is optimizing itself as it plays.
|
-
I have really started to like my Samsung Blackjack running Windows Mobile lately. I am going to start to share some of my favorite applications and gadgets as time permits. My latest and probably favorite as of now is Live Search for Windows Mobile. I am not going to run down all the features, but share with you how I managed to get GPS working and how COOL this is. If you haven't seen it, check it out. I really think they need to give more information on this site, as I am sure that more people would be sprinting to get to it. (http://livesearchmobile.com/?mid=1011) I will let you know that I am a real skeptic when it comes to useful applications that run on phones. I am not talking about email, document readers or the host of "pocket utilities" that are available. I heard the term used this week "game changer" and that is what I feel Live Search is for Mobile. Most applications on the phone are just an afterthought for me. But now, I seem to have need to use this more than I ever thought possible. Read on... After I browsed to the link that is provided on the web site above, the cab downloaded and installed with no problems. The first thing I notice is how clean the interface is and very intuitive. There is virtually no learning curve on how to use the application and there is not 100 clicks needed to navigate where you want to go. 
The interface comes up as shown so I want to find a pizza joint close to where I am here in Manhattan. I type in Pizza, hit my enter key on my QWERTY keyboard and BAM! 
No big deal, but it was fast and I did not even have to think about what I was doing. Knowing how Windows Mobile works, I can navigate down to the phone number for Pizza Cave, and push to dial. If that is all it did, I would be pretty happy! But my fingers started to wander, and hit the menu button, and WOW! 
If I had to create a wish list of what else I would like to do, there it is! Now I am sitting straight up in my chair, eyes focused, slight grin creeping in. So I can get directions to there, from there, save to my contacts and instantly SMS it to a friend. How awesome is that? So I decide to click the Map button, expecting to see one city block out of focus... 
After what seemed like 30 seconds, I finally close my mouth. Very very cool! I start to visualize never getting lost again. Finding hidden subway stations has always challenged me for some reason. But now, by just typing in my starting point, I can find just about anything that is nearby! With a content look on my face, I start to fade back off this happy cloud I am on, so as I am clicking Menu again to exit the map, when... 
What's this? Center on GPS! Did I read that right? If this is true, I will have completed the circle...so I start searching and find this great article that tells how to pair a GPS device with the BlackJack. This site is also a good resource for BlackJack phone users. (http://www.i607.org/wiki/doku.php?id=enable_bluetooth_gps) The instructions are pretty clear, but I had to tweak a few things to get it just right. I made the adjustments to my BlackJack registry... 
After I pair my G-SAT BT-GPS-3525EF Bluetooth GPS with my BlackJack... 
I am off to test it! As soon as I select Center on GPS... 
In the upper right hand corner I see the magic words. Getting GPS position...and...BAM! 
There I am, and there is the green marker for my Pizza Joint! One last thing, gotta get there...so I map the directions.. 
So there you go. The last thing I would like to point out, is the ease of this application and how comfortable it feels using it. That is not something I say about a lot of Mobile applications. This is a Game Changer for me!
|
-
-
This function returns an integer of the hour of the year passed as a variable. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_GetHourOfYear]')
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [util].[uf_GetHourOfYear]
GO
CREATE FUNCTION [util].[uf_GetHourOfYear](
@date DATETIME
)
RETURNS INTEGER
WITH EXECUTE AS CALLER
AS
/**********************************************************************************************************
* UDF Name:
* [util].[uf_GetHourOfYear]
* Parameters:
* @date datetime - The date to convert
* Purpose: This function returns an integer of the hour of the year passed as a variable.
*
* Example:
select [util].[uf_GetHourOfYear](GETDATE())
*
* Revision Date/Time:
* August 1, 2007
*
**********************************************************************************************************/
BEGIN
-- declare variables
DECLARE @result integer;
SET @result = (DATEPART(dy,@DATE) - 1) * 24 + DATEPART(hh,@DATE);
-- return results.
RETURN @result;
END;
GO
SELECT [util].[uf_GetHourOfYear](GETDATE());
GO
|
-
This function returns an integer of the hour of the month passed as a variable. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_GetHourOfMonth]')
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [util].[uf_GetHourOfMonth]
GO
CREATE FUNCTION [util].[uf_GetHourOfMonth](
@date DATETIME
)
RETURNS INTEGER
WITH EXECUTE AS CALLER
AS
/**********************************************************************************************************
* UDF Name:
* [util].[uf_GetHourOfMonth]
* Parameters:
* @date datetime - The date to convert
* Purpose: This function returns an integer of the hour of the month passed as a variable.
*
* Example:
select [util].[uf_GetHourOfMonth](GETDATE())
*
* Revision Date/Time:
* August 1, 2007
*
**********************************************************************************************************/
BEGIN
-- declare variables
DECLARE @result integer;
SET @result = (DAY(@DATE) - 1) * 24 + DATEPART(hh,@DATE);
-- return results.
RETURN @result;
END;
GO
SELECT [util].[uf_GetHourOfMonth](GETDATE());
GO
|
-
This function returns an integer of the number of days in the half year. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_GetHalfYearDays]')
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [util].[uf_GetHalfYearDays]
GO
CREATE FUNCTION [util].[uf_GetHalfYearDays](
@date DATETIME
)
RETURNS INTEGER
WITH EXECUTE AS CALLER
AS
/**********************************************************************************************************
* UDF Name:
* [util].[uf_GetHalfYearDays]
* Parameters:
* @date datetime - The date to convert
* Purpose: This function returns an integer of the number of days in the half year.
*
* Example:
select [util].[uf_GetHalfYearDays](GETDATE())
*
* Revision Date/Time:
* August 1, 2007
*
**********************************************************************************************************/
BEGIN
-- declare variables
DECLARE @result integer;
DECLARE @month integer;
DECLARE @halfdate DATETIME;
-- determine half year date
SET @halfdate = CAST(CAST(((((MONTH(@date) - 1) / 6) * 6) + 1) AS VARCHAR) + '-1-' + CAST(YEAR(@date) AS VARCHAR) AS DATETIME);
-- calculate days
SET @result = DATEDIFF(DAY,@halfdate,@date);
-- return results.
RETURN @result;
END;
GO
SELECT [util].[uf_GetHalfYearDays](GETDATE());
GO
|
-
I would have to say that one of my favorite new utilities that shipped with SQL Server 2005 has been the SQLCMD utility. I am going to demonstrate the use of include files. The following is the complete call syntax for SQLCMD. sqlcmd [{ { -U login_id [ -P password ] } | –E trusted connection }] [ -z new password ] [ -Z new password and exit] [ -S server_name [ \ instance_name ] ] [ -H wksta_name ] [ -d db_name ] [ -l login time_out ] [ -A dedicated admin connection ] [ -i input_file ] [ -o output_file ] [ -f < codepage > | i: < codepage > [ < , o: < codepage > ] ] [ -u unicode output ] [ -r [ 0 | 1 ] msgs to stderr ] [ -R use client regional settings ] [ -q "cmdline query" ] [ -Q "cmdline query" and exit ] [ -e echo input ] [ -t query time_out ] [ -I enable Quoted Identifiers ] [ -v var = "value"...] [ -x disable variable substitution ] [ -h headers ][ -s col_separator ] [ -w column_width ] [ -W remove trailing spaces ] [ -k [ 1 | 2 ] remove[replace] control characters ] [ -y display_width ] [-Y display_width ] [ -b on error batch abort ] [ -V severitylevel ] [ -m error_level ] [ -a packet_size ][ -c cmd_end ] [ -L [ c ] list servers[clean output] ] [ -p [ 1 ] print statistics[colon format] ] [ -X [ 1 ] ] disable commands, startup script, enviroment variables [and exit] [ -? show syntax summary ]
:r is a SQLCMD command that parses additional Transact-SQL statements and sqlcmd commands from the file specified by <filename> into the statement cache.
A simple example:
In SQLQuery1.sql I have the following:
SELECT TOP 5 ProductID,Name,ProductNumber,MakeFlag FROM AdventureWorks.Production.Product
In SQLQuery2.sql I can then reference SQL file 1 as follows:
:r "c:\SQLQuery1.sql"
My results are:
ProductID Name ProductNumber MakeFlag
1 Adjustable Race AR-5381 0
2 Bearing Ball BA-8327 0
3 BB Ball Bearing BE-2349 1
4 Headset Ball Bearings BE-2908 0
316 Blade BL-2036 1
This may not seem like a big deal, but consider the following scenario. I have a large bat file of SQL that I process in jobs or in SSIS packages. This bat file is doing and setting the same variables over and over again, so I can create include files that declare my variables, and another include file that sets them.
My first file "c:\DeclareVariables.sql" will always change me to the correct DB, set no count on and declare my variables. This becomes good anchor file for any pre-processing that I may want to do.
USE [AdventureWorks] GO SET NOCOUNT ON GO DECLARE @AccountingStartDate smalldatetime; DECLARE @AccountingEndDate smalldatetime; DECLARE @DocumentStatusText varchar(400); DECLARE @Status tinyint;
"c:\SetVariables.sql" is where is set my variables. You notice that the SQL variable @Status is set to a SQLCMD variable of the same name "Status". This allows me to control that from my batch sql file. I am trying to eliminate any hard coded references in my global files.
SET @Status = $(Status); SET @AccountingStartDate = (SELECT AdventureWorks.dbo.ufnGetAccountingStartDate()); SET @AccountingEndDate = (SELECT AdventureWorks.dbo.ufnGetAccountingEndDate()); SET @DocumentStatusText = (SELECT AdventureWorks.dbo.ufnGetDocumentStatusText(@Status));
And finally the batch file "c:\SQLBatch.sql". The first two lines set my include files to variables. This allows for a consistent naming through all my files and allows me to change the files without having to change the batch code. This is good for testing and portability. The next line is where I set the SQLCMD "Status" variable. This variable is then set in the SetVariables.sql file to the TSQL variable @Status that is passed to the function ufnGetDocumentStatusText.
:setvar DeclareVariablesScript "c:\DeclareVariables.sql" :setvar SetVariablesScript "c:\SetVariables.sql" -- Set Local variable Status (Used in SetVariablesScript) :setvar Status 1 -- Declare Global Script Variables :r $(DeclareVariablesScript) -- Set Global Script Variables :r $(SetVariablesScript) SELECT * FROM AdventureWorks.Sales.SalesOrderHeader soh WHERE ((soh.OrderDate > = @AccountingStartDate) AND (soh.OrderDate <= @AccountingEndDate)); GO
Now all I have to do it execute the file "c:\SQLBatch.sql". If you are familiar with ASP include files this should be easy to understand.
The more you use this utility the more you will enjoy it. I will be posting more topics on SQLCMD in the future. Stay Tuned!
|
-
These tables can be used for testing various data types of SQL Server. You can extend as needed. SET NUMERIC_ROUNDABORT OFF; SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,QUOTED_IDENTIFIER, ANSI_NULLS ON; GO -- Create XML Schema Collection CREATE XML SCHEMA COLLECTION PartitionMapSchema AS N'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="Partition_Map"> <xs:complexType> <xs:sequence> <xs:element maxOccurs="unbounded" name="Value" type="xs:date" /> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>' ; GO -- Create Table DataType CREATE TABLE [dbo].[Table_DataType]( [Table_DataType_ID] [bigint] IDENTITY(1,1) NOT NULL, [FK_Table_FK_01_ID] [bigint] NOT NULL, [col_guid] [uniqueidentifier] ROWGUIDCOL NULL CONSTRAINT [Guid_Default] DEFAULT (newsequentialid()), [col_bigint] [bigint] NOT NULL CONSTRAINT [DF_Table_DataType_col_bigint] DEFAULT ((0)), [col_bit] [bit] NOT NULL CONSTRAINT [DF_Table_DataType_col_bit] DEFAULT ((1)), [col_binary] [binary](1) NULL CONSTRAINT [DF_Table_DataType_col_binary] DEFAULT ((2)), [col_char] AS (left([col_nchar],N'3')), [col_datetime] AS (getdate()), [col_decimal] [decimal](18, 9) NULL CONSTRAINT [DF_Table_DataType_col_decimal] DEFAULT ((5)), [col_float] [float] NULL CONSTRAINT [DF_Table_DataType_col_float] DEFAULT ((6)), [col_image] [image] NULL, [col_int] [int] NOT NULL CONSTRAINT [DF_Table_DataType_col_int] DEFAULT ((8)), [col_money] [money] NOT NULL CONSTRAINT [DF_Table_DataType_col_money] DEFAULT ((9)), [col_nchar] [nchar](100) NULL CONSTRAINT [DF_Table_DataType_col_nchar] DEFAULT (N'10'), [col_ntext] [ntext] NOT NULL CONSTRAINT [DF_Table_DataType_col_ntext] DEFAULT (N'11'), [col_numeric] [numeric](18, 9) NOT NULL CONSTRAINT [DF_Table_DataType_col_numeric] DEFAULT ((12)), [col_nvarchar] [nvarchar](256) NOT NULL CONSTRAINT [DF_Table_DataType_col_nvarchar] DEFAULT (N'13'), [col_real] [real] NOT NULL CONSTRAINT [DF_Table_DataType_col_real] DEFAULT (N'14'), [col_smalldatetime] [smalldatetime] NOT NULL CONSTRAINT [DF_Table_DataType_col_smalldatetime] DEFAULT (N'15'), [col_smallint] [smallint] NOT NULL CONSTRAINT [DF_Table_DataType_col_smallint] DEFAULT ((16)), [col_smallmoney] [smallmoney] NOT NULL CONSTRAINT [DF_Table_DataType_col_smallmoney] DEFAULT ((17)), [col_sql_variant] [sql_variant] NOT NULL CONSTRAINT [DF_Table_DataType_col_sql_variant] DEFAULT ((18)), [col_sysname] [sysname] NULL, [col_text] [text] NOT NULL CONSTRAINT [DF_Table_DataType_col_text] DEFAULT ('20'), [col_timestamp] [timestamp] NULL, [col_tinyint] [tinyint] NOT NULL CONSTRAINT [DF_Table_DataType_col_tinyint] DEFAULT ((22)), [col_uniqueidentifier] [uniqueidentifier] NULL, [col_varbinary] [varbinary](1) NOT NULL CONSTRAINT [DF_Table_DataType_col_varbinary] DEFAULT ((24)), [col_varchar] [varchar](1024) NOT NULL CONSTRAINT [DF_Table_DataType_col_varchar] DEFAULT ('25'), [col_xml] [xml](CONTENT [dbo].[PartitionMapSchema]) NULL CONSTRAINT [DF_Table_DataType_col_xml] DEFAULT (CAST(N'<Partition_Map><Value>1900-01-01</Value><Value>2049-12-31</Value></Partition_Map>' AS xml)), CONSTRAINT [PK_Table_DataType] PRIMARY KEY CLUSTERED ( [Table_DataType_ID] ASC, [col_bigint] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO -- Create Table FK CREATE TABLE [dbo].[Table_FK_01]( [Table_FK_01_ID] [bigint] IDENTITY(1,1), [col_bigint] [bigint] NOT NULL CONSTRAINT [DF_Table_FK_01_col_bigint] DEFAULT ((0)), -- 1 CONSTRAINT [PK_Table_FK_01] PRIMARY KEY CLUSTERED ( [Table_FK_01_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO -- Create Constraints ALTER TABLE [dbo].[Table_DataType] WITH CHECK ADD CONSTRAINT [FK_Table_DataType_Table_FK_01] FOREIGN KEY([FK_Table_FK_01_ID]) REFERENCES [dbo].[Table_FK_01] ([Table_FK_01_ID]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[Table_DataType] CHECK CONSTRAINT [FK_Table_DataType_Table_FK_01] GO
|
-
Here is a view that contains column information. This is useful if you need to create a DDL statement for a table. CREATE VIEW dbo.vColumnInfo AS SELECT tbl.name AS [Table_Name], SCHEMA_NAME(tbl.schema_id) AS [Table_Schema], CAST(ISNULL(cik.index_column_id , 0) AS BIT) AS [InPrimaryKey], CAST(ISNULL(( SELECT TOP 1 1 FROM sys.foreign_key_columns AS colfk WHERE colfk.parent_column_id = clmns.column_id AND colfk.parent_object_id = clmns.object_id ) , 0) AS BIT) AS [IsForeignKey], QUOTENAME(clmns.name) AS [Column_Name], clmns.object_id AS object_id, clmns.column_id AS column_id, clmns.is_computed AS Is_Computed, ISNULL(cc.definition , N'') AS computed_column_definition, QUOTENAME(usrt.name) + CASE usrt.name WHEN 'binary' THEN CASE clmns.max_length WHEN -1 THEN '(max) ' ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20)) + ') ' END WHEN 'char' THEN CASE clmns.max_length WHEN -1 THEN '(max) ' ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20)) + ') ' END WHEN 'decimal' THEN ' (' + CAST(clmns.precision AS VARCHAR(20)) + ',' + CAST(clmns.max_length AS VARCHAR(20)) + ') ' WHEN 'nchar' THEN CASE clmns.max_length WHEN -1 THEN '(max) ' ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20)) + ') ' END WHEN 'numeric' THEN ' (' + CAST(clmns.precision AS VARCHAR(20)) + ',' + CAST(clmns.max_length AS VARCHAR(20)) + ') ' WHEN 'nvarchar' THEN CASE clmns.max_length WHEN -1 THEN '(max) ' ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20)) + ') ' END WHEN 'varbinary' THEN CASE clmns.max_length WHEN -1 THEN '(max) ' ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20)) + ') ' END WHEN 'varchar' THEN CASE clmns.max_length WHEN -1 THEN '(max) ' ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20)) + ') ' END WHEN 'xml' THEN CASE WHEN ISNULL(xscclmns.name , N'') = '' THEN '' ELSE ' (CONTENT ' + QUOTENAME(s2clmns.name) + '.' + QUOTENAME(xscclmns.name) + ') ' END ELSE '' END AS Data_Type, clmns.is_identity AS is_identity, CASE clmns.is_identity WHEN 1 THEN ' IDENTITY (' + CAST(ISNULL(ic.seed_value , 0) AS VARCHAR(10)) + ',' + CAST(ISNULL(ic.increment_value , 0) AS VARCHAR(10)) + ') ' ELSE ' ' END AS [Identity], clmns.is_nullable AS is_nullable, CASE clmns.is_nullable WHEN 1 THEN ' NULL ' ELSE ' NOT NULL ' END AS [Nullable], CASE WHEN cstr.name <> '' THEN ' CONSTRAINT ' + QUOTENAME(cstr.name) + ' DEFAULT ' + cstr.definition + ',' ELSE '' END AS [Constraint], QUOTENAME(clmns.name) + ' ' + CASE clmns.is_computed WHEN 1 THEN ' AS ' + ISNULL(cc.definition , N'') + ',' ELSE QUOTENAME(usrt.name) + CASE usrt.name WHEN 'binary' THEN CASE clmns.max_length WHEN -1 THEN '(max) ' ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20)) + ') ' END WHEN 'char' THEN CASE clmns.max_length WHEN -1 THEN '(max) ' ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20)) + ') ' END WHEN 'decimal' THEN ' (' + CAST(clmns.precision AS VARCHAR(20)) + ',' + CAST(clmns.max_length AS VARCHAR(20)) + ') ' WHEN 'nchar' THEN CASE clmns.max_length WHEN -1 THEN '(max) ' ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20)) + ') ' END WHEN 'numeric' THEN ' (' + CAST(clmns.precision AS VARCHAR(20)) + ',' + CAST(clmns.max_length AS VARCHAR(20)) + ') ' WHEN 'nvarchar' THEN CASE clmns.max_length WHEN -1 THEN '(max) ' ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20)) + ') ' END WHEN 'varbinary' THEN CASE clmns.max_length WHEN -1 THEN '(max) ' ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20)) + ') ' END WHEN 'varchar' THEN CASE clmns.max_length WHEN -1 THEN '(max) ' ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20)) + ') ' END WHEN 'xml' THEN CASE WHEN ISNULL(xscclmns.name , N'') = '' THEN '' ELSE ' (CONTENT ' + QUOTENAME(s2clmns.name) + '.' + QUOTENAME(xscclmns.name) + ') ' END ELSE '' END + CASE clmns.is_identity WHEN 1 THEN ' IDENTITY (' + CAST(ISNULL(ic.seed_value , 0) AS VARCHAR(10)) + ',' + CAST(ISNULL(ic.increment_value , 0) AS VARCHAR(10)) + ') ' ELSE ' ' END + CASE clmns.is_nullable WHEN 1 THEN ' NULL ' ELSE ' NOT NULL ' END + CASE WHEN cstr.name <> '' THEN ' CONSTRAINT ' + QUOTENAME(cstr.name) + ' DEFAULT ' + cstr.definition + ',' ELSE ',' END END AS [Column_Text] FROM sys.tables AS tbl INNER JOIN sys.all_columns AS clmns ON clmns.object_id = tbl.object_id LEFT OUTER JOIN sys.indexes AS ik ON ik.object_id = clmns.object_id AND 1 = ik.is_primary_key LEFT OUTER JOIN sys.index_columns AS cik ON cik.index_id = ik.index_id AND cik.column_id = clmns.column_id AND cik.object_id = clmns.object_id AND 0 = cik.is_included_column LEFT OUTER JOIN sys.computed_columns AS cc ON cc.object_id = clmns.object_id AND cc.column_id = clmns.column_id LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = clmns.system_type_id AND baset.user_type_id = baset.system_type_id LEFT OUTER JOIN sys.schemas AS sclmns ON sclmns.schema_id = usrt.schema_id LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.object_id AND ic.column_id = clmns.column_id LEFT OUTER JOIN sys.objects AS d ON d.object_id = clmns.default_object_id LEFT OUTER JOIN sys.objects AS r ON r.object_id = clmns.rule_object_id LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns ON xscclmns.xml_collection_id = clmns.xml_collection_id LEFT OUTER JOIN sys.schemas AS s2clmns ON s2clmns.schema_id = xscclmns.schema_id LEFT OUTER JOIN sys.default_constraints AS cstr ON cstr.object_id = clmns.default_object_id
The select statement:
SELECT Column_Text FROM dbo.vColumnInfo
ORDER BY
object_id,
column_id
Returns:
[Table_DataType_ID] [bigint] IDENTITY (1,1) NOT NULL ,
[FK_Table_FK_01_ID] [bigint] NOT NULL ,
[col_guid] [uniqueidentifier] NULL CONSTRAINT [Guid_Default] DEFAULT (newsequentialid()),
[col_bigint] [bigint] NOT NULL CONSTRAINT [DF_Table_DataType_col_bigint] DEFAULT ((0)),
[col_bit] [bit] NOT NULL CONSTRAINT [DF_Table_DataType_col_bit] DEFAULT ((1)),
[col_binary] [binary] (0) NULL CONSTRAINT [DF_Table_DataType_col_binary] DEFAULT ((2)),
[col_char] AS (left([col_nchar],N'3')),
[col_datetime] AS (getdate()),
[col_decimal] [decimal] (18,9) NULL CONSTRAINT [DF_Table_DataType_col_decimal] DEFAULT ((5)),
[col_float] [float] NULL CONSTRAINT [DF_Table_DataType_col_float] DEFAULT ((6)),
[col_image] [image] NULL ,
[col_int] [int] NOT NULL CONSTRAINT [DF_Table_DataType_col_int] DEFAULT ((8)),
[col_money] [money] NOT NULL CONSTRAINT [DF_Table_DataType_col_money] DEFAULT ((9)),
[col_nchar] [nchar] (100) NULL CONSTRAINT [DF_Table_DataType_col_nchar] DEFAULT (N'10'),
[col_ntext] [ntext] NOT NULL CONSTRAINT [DF_Table_DataType_col_ntext] DEFAULT (N'11'),
[col_numeric] [numeric] (18,9) NOT NULL CONSTRAINT [DF_Table_DataType_col_numeric] DEFAULT ((12)),
[col_nvarchar] [nvarchar] (256) NOT NULL CONSTRAINT [DF_Table_DataType_col_nvarchar] DEFAULT (N'13'),
[col_real] [real] NOT NULL CONSTRAINT [DF_Table_DataType_col_real] DEFAULT (N'14'),
[col_smalldatetime] [smalldatetime] NOT NULL CONSTRAINT [DF_Table_DataType_col_smalldatetime] DEFAULT (N'15'),
[col_smallint] [smallint] NOT NULL CONSTRAINT [DF_Table_DataType_col_smallint] DEFAULT ((16)),
[col_smallmoney] [smallmoney] NOT NULL CONSTRAINT [DF_Table_DataType_col_smallmoney] DEFAULT ((17)),
[col_sql_variant] [sql_variant] NOT NULL CONSTRAINT [DF_Table_DataType_col_sql_variant] DEFAULT ((18)),
[col_sysname] [sysname] NULL ,
[col_text] [text] NOT NULL CONSTRAINT [DF_Table_DataType_col_text] DEFAULT ('20'),
[col_timestamp] [timestamp] NULL ,
[col_tinyint] [tinyint] NOT NULL CONSTRAINT [DF_Table_DataType_col_tinyint] DEFAULT ((22)),
[col_uniqueidentifier] [uniqueidentifier] NULL ,
[col_varbinary] [varbinary] (0) NOT NULL CONSTRAINT [DF_Table_DataType_col_varbinary] DEFAULT ((24)),
[col_varchar] [varchar] (512) NOT NULL CONSTRAINT [DF_Table_DataType_col_varchar] DEFAULT ('25'),
[col_xml] [xml] (CONTENT [dbo].[PartitionDemoSchemaCollection]) NULL ,
For the table definition:
CREATE TABLE [dbo].[Table_DataType]( [Table_DataType_ID] [bigint] IDENTITY(1,1) NOT NULL, [FK_Table_FK_01_ID] [bigint] NOT NULL, [col_guid] [uniqueidentifier] ROWGUIDCOL NULL CONSTRAINT [Guid_Default] DEFAULT (newsequentialid()), [col_bigint] [bigint] NOT NULL CONSTRAINT [DF_Table_DataType_col_bigint] DEFAULT ((0)), [col_bit] [bit] NOT NULL CONSTRAINT [DF_Table_DataType_col_bit] DEFAULT ((1)), [col_binary] [binary](1) NULL CONSTRAINT [DF_Table_DataType_col_binary] DEFAULT ((2)), [col_char] AS (left([col_nchar],N'3')), [col_datetime] AS (getdate()), [col_decimal] [decimal](18, 9) NULL CONSTRAINT [DF_Table_DataType_col_decimal] DEFAULT ((5)), [col_float] [float] NULL CONSTRAINT [DF_Table_DataType_col_float] DEFAULT ((6)), [col_image] [image] NULL, [col_int] [int] NOT NULL CONSTRAINT [DF_Table_DataType_col_int] DEFAULT ((8)), [col_money] [money] NOT NULL CONSTRAINT [DF_Table_DataType_col_money] DEFAULT ((9)), [col_nchar] [nchar](100) NULL CONSTRAINT [DF_Table_DataType_col_nchar] DEFAULT (N'10'), [col_ntext] [ntext] NOT NULL CONSTRAINT [DF_Table_DataType_col_ntext] DEFAULT (N'11'), [col_numeric] [numeric](18, 9) NOT NULL CONSTRAINT [DF_Table_DataType_col_numeric] DEFAULT ((12)), [col_nvarchar] [nvarchar](256) NOT NULL CONSTRAINT [DF_Table_DataType_col_nvarchar] DEFAULT (N'13'), [col_real] [real] NOT NULL CONSTRAINT [DF_Table_DataType_col_real] DEFAULT (N'14'), [col_smalldatetime] [smalldatetime] NOT NULL CONSTRAINT [DF_Table_DataType_col_smalldatetime] DEFAULT (N'15'), [col_smallint] [smallint] NOT NULL CONSTRAINT [DF_Table_DataType_col_smallint] DEFAULT ((16)), [col_smallmoney] [smallmoney] NOT NULL CONSTRAINT [DF_Table_DataType_col_smallmoney] DEFAULT ((17)), [col_sql_variant] [sql_variant] NOT NULL CONSTRAINT [DF_Table_DataType_col_sql_variant] DEFAULT ((18)), [col_sysname] [sysname] NULL, [col_text] [text] NOT NULL CONSTRAINT [DF_Table_DataType_col_text] DEFAULT ('20'), [col_timestamp] [timestamp] NULL, [col_tinyint] [tinyint] NOT NULL CONSTRAINT [DF_Table_DataType_col_tinyint] DEFAULT ((22)), [col_uniqueidentifier] [uniqueidentifier] NULL, [col_varbinary] [varbinary](1) NOT NULL CONSTRAINT [DF_Table_DataType_col_varbinary] DEFAULT ((24)), [col_varchar] [varchar](1024) NOT NULL CONSTRAINT [DF_Table_DataType_col_varchar] DEFAULT ('25'), [col_xml] [xml](CONTENT [dbo].[PartitionDemoSchemaCollection]) NULL, CONSTRAINT [PK_Table_DataType] PRIMARY KEY CLUSTERED ( [Table_DataType_ID] ASC, [col_bigint] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
|
-
Here is the link. http://support.microsoft.com/default.aspx/kb/939537 Here are the fixes in this release. SQL bug number
KB article number
Description 50001581
938243 (http://support.microsoft.com/kb/938243/)
FIX: Error message when you run a full-text query against a catalog in SQL Server 2005: “The execution of a full-text query failed. The content index is corrupt." 50001585
938712 (http://support.microsoft.com/kb/938712/)
FIX: Some records in the fact table may not be processed when you process a dimension that contains many attributes or many members in SQL Server 2005 50001499
938962 (http://support.microsoft.com/kb/938962/)
FIX: You may receive an access violation or error 942 when you drop a database in SQL Server 2005 50001524
939563 (http://support.microsoft.com/kb/939563/)
FIX: Error message when you synchronize a merge replication in Microsoft SQL Server 2005: "MSmerge_del_<GUID>, Line 42 String or binary data would be truncated" 50001582
939942 (http://support.microsoft.com/kb/939942/)
FIX: You receive an error message when you try to access a report after you configure SQL Server 2005 Reporting Services to run under the SharePoint integrated mode 50001583
940128 (http://support.microsoft.com/kb/940128/)
FIX: You receive error 8623 when you run a complex query in SQL Server 2005 50001586
940129 (http://support.microsoft.com/kb/940129/)
FIX: An MDX query does not return results when you execute the query against a cube that contains an unnatural hierarchy in a dimension in SQL Server 2005 Analysis Services 50001517
940160 (http://support.microsoft.com/kb/940160/)
FIX: Error message when you deploy a SQL Server 2005 Analysis Service project that contains many cubes, and the cubes contain linked measure groups: "Unexpected internal error" 50001449
940210 (http://support.microsoft.com/kb/940210/)
FIX: Error message when you try to insert more than 3 megabytes of data into a distributed partitioned view in SQL Server 2005: "A system assertion check has failed" 50001447
940220 (http://support.microsoft.com/kb/940220/)
FIX: Error message when you run a SQL Server 2005 Integration Services package that contains an FTP task: “An error occurred in the requested FTP operation" 50001448
940221 (http://support.microsoft.com/kb/940221/)
FIX: Error message when you try to create an Oracle publication by using the New Publication Wizard in Microsoft SQL Server 2005 Service Pack 2: “OLE DB Provider "OraOLEDB.ORACLE" for Linked server <LinkedServerName> returned message "ORA-02074: Cannot RO 50001451
940223 (http://support.microsoft.com/kb/940223/)
FIX: Error message when you synchronize a subscription by using Windows Synchronization Manager in SQL Server 2005: “The merge process failed to get correct information about the Interactive Resolver component from the Registry" 50001416
940260 (http://support.microsoft.com/kb/940260/)
FIX: Error message when you use Service Broker in SQL Server 2005: "An error occurred while receiving data: '64(The specified network name is no longer available.)'" 50001435
940269 (http://support.microsoft.com/kb/940269/)
FIX: Error message when you try to edit a SQL Server Agent job or a maintenance plan by using SQL Server Management Studio in SQL Server 2005: "String or binary data would be truncated" 50001567
940281 (http://support.microsoft.com/kb/940281/)
FIX: An access violation may occur, and you may receive an error message, when you query the sys.dm_exe_sessions dynamic management view in SQL Server 2005 50001351
940370 (http://support.microsoft.com/kb/940370/)
FIX: The "User:" prefix is lost for the event information that is generated by a child package in SQL Server 2005 Integration Services after you install SQL Server 2005 Service Pack 2 50001382
940373 (http://support.microsoft.com/kb/940373/)
FIX: The performance of a Multidimensional Expressions query in SQL Server 2005 Analysis Services Service Pack 2 is much slower than the performance in earlier builds of SQL Server 2005 Analysis Services 50001433
940378 (http://support.microsoft.com/kb/940378/)
Fix: Unable to Change Transaction Isolation Level After Cursor Declaration 50001479
940384 (http://support.microsoft.com/kb/940384/)
FIX: You receive a System.InvalidCastException exception when you run an application that calls the Server.JobServer.Jobs.Contains method on a computer that has SQL Server 2005 Service Pack 2 installed 50001494
940386 (http://support.microsoft.com/kb/940386/)
FIX: You cannot install SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies on a beta version of Windows Server 2008-based computer 50001602
940545 (http://support.microsoft.com/kb/940545/)
FIX: The performance of insert operations against a table that contains an identity column may be slow in SQL Server 2005 50001589
940935 (http://support.microsoft.com/kb/940935/)
FIX: Error message when you run a parallel query in SQL Server 2005 on a computer that has multiple processors: “SQL Server Assertion: File: <lckmgr.cpp>, line=10850 Failed Assertion = 'GetLocalLockPartition () == xactLockInfo->GetLocalLockPartition ()'" 50001599
940937 (http://support.microsoft.com/kb/940937/)
FIX: Error message when you try to update the index key columns of a non-unique clustered index in SQL Server 2005: " Cannot insert duplicate key row in object 'ObjectName' with unique index 'IndexName'" 50001609
940939 (http://support.microsoft.com/kb/940939/)
FIX: Data is not rolled back after you roll back a transaction that contains a writeback operation in SQL Server 2005 Analysis Services 50001629
940942 (http://support.microsoft.com/kb/940942/)
FIX: Error message when you run a stored procedure that references a table that is upgraded from SQL Server 2000 to SQL Server 2005: “A time-out occurred while waiting for buffer latch" 50001573
940949 (http://support.microsoft.com/kb/940949/)
FIX: You receive an error message when you run an UPDATE CUBE statement to update a cube in SQL Server 2005 Analysis Services 50001576
940962 (http://support.microsoft.com/kb/940962/)
FIX: When processing a dimension on SQL Server 2005 Analysis Services an error is raised with the following format: "The following file is corrupted: Physical file: \\?\<datafilepath>\MSMDBuildLevelStores_avl_672_29775_njzs2.tmp. Logical file ." 50001511
940126 (http://support.microsoft.com/kb/940126/)
FIX: Error 9003 is logged in the SQL Server error log file when you use log shipping in SQL Server 2005 50001436
940379 (http://support.microsoft.com/kb/940379/)
FIX: Error message when you use the UNLOAD and REWIND options to back up a database to a tape device in SQL Server 2005: "Operation on device '<TapeDevice>' exceeded retry count" 50001412
940375 (http://support.microsoft.com/kb/940375/)
FIX: Error message when you use the Copy Database Wizard to move a database from SQL Server 2000 to SQL Server 2005 50001522
939562 (http://support.microsoft.com/kb/939562/)
FIX: Error message when you run a query that fires an INSTEAD OF trigger in SQL Server 2005 Service Pack 2: "Internal Query Processor Error The query processor could not produce a query plan" 50001224
937100 (http://support.microsoft.com/kb/937100/)
FIX: Error message when you run a SQL Server 2005 Integration Services package that contains a Script Component transformation: "Insufficient memory to continue the execution of the program" 50001415
940377 (http://support.microsoft.com/kb/940377/)
FIX: Error message when you process cubes for one of the named instances of SQL Server 2005 Analysis Services: "Error opening file" 50001523
938363 (http://support.microsoft.com/kb/938363/)
FIX: Data is not replicated to a subscriber in a different partition by using parameterized row filters in SQL Server 2005 50000872
The MDX query performance is slow in SQL Server 2005 Analysis Services because SQL Server 2005 Analysis Services does not reuse the data cache 50001109
The dta utility stops unexpectedly and an exception occurs in SQL Server 2005 50001224
When you run a SQL Server 2005 Integration Services package that uses the VariableDispenser class, the package fails and you receive an error message 50001365
After you install SQL Server 2005 Service Pack 2, you receive error 8624 if the result set of a fast forward cursor contains a certain number of columns 50001368
After you install SQL Server 2005 Service Pack 2, the performance of a MDX query is 10 times slower than the performance on SQL Server 2005 Analysis Services build 1555 50001396
When you open a SQL Server 2005 Reporting Services report after you install SQL Server 2005 Service pack 2, the parameter toolbar and the report toolbar does not appear correctly if you specify the SP_Full value for the rc:StyleSheet URL access parameter on a report URL 50001412
Error message when you use the Copy Database Wizard to move a database from SQL Server 2000 to SQL Server 2005: "Cannot drop database "Database_Name" because it is currently in use.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly" 50001414
Error message when you use the bcp utility together with the queryout option to bulk copy data from SQL Server 2005 to a file: "SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native Client]BCP host-files must contain at least onecolumn" 50001415
After you install SQL Server 2005 Service Pack 2, the Msmdredir.ini file is frequently updated by each instance of SQL Server 2005 50001436
Error message when you use the BACKUP DATABASE statement together with the UNLOAD option and with the REWIND option to back up a tape device in SQL Server 2005: "Operation on device 'TAPE0(<\\.\Tape0>)' exceeded retry count" 50001461
When some MDX queries are executed at the same for the same role or for the same user in SQL Server 2005 Analysis Services, the CPU usage is very high 50001475
When you open a report that contains a date and time picker (DTP) control in SQL Server 2005 Reporting Services, the format of the DTP control appears incorrectly 50001511
Error 9003 occurs when you restore a transaction log backup in SQL Server 2005 50001520
The query syntax of a report is changed when you run the report in SQL Server 2005 Reporting Services Service Pack 2 on SAP BW 3.5 50001522
Error 8624 occurs when you run a query in SQL Server 2005 Service Pack 2 (SP2) or later versions. However, you can successfully run the query in pre-SP2 version of SQL Server 2005 50001523
After you install SQL Server 2005 Service Pack 2, publications that use precomputed partitions can cause the non-convergence of data 50001525
Error message when you use SQL Server Agent to run jobs by using a proxy account: " SQLServer Error: 22046, Encryption error using CryptProtectData, CryptUnprotectData failed (1723)" 50001526
Using SQL Server Agent to run jobs in the context of a proxy account may fail with error "SQLServer Error: 22046, Encryption error using CryptProtectData, CryptUnprotectData failed (1723)" 50001529
The performance of SQL Server 2005 decreases because SQL Server 2005 is waiting for access to memory objects which is indicated by the CMEMTHREAD waittype 50001578
An exception occurs in SQL Native Client: "New request is not allowed to start because it should come with valid transaction descriptor" 50001579
When you use SQL Native Client to retrieve a value in a column of the TEXT data type, you obtain incorrect result if the value contains more than 1024 characters 50001580
Memory leak of the TokenAndPermAccessCheckResult entries occurs in SQL Server 2005 50001595
When you use SQL Native Client for a connection, the connection switches to use the auto-commit mode from the manually-commit mode 50001598
SQL Native Client overwrite error codes which causes that you do not receive informative error messages when some operations fails 50001639
The performance of an INSERT statement or an UPDATE statement that uses the result from a query is much slower in SQL Server 2005 Service Pack 2 than in SQL Server 2005 Service Pack 1 or earlier versions if the query uses the nodes method 50001164
FIX: Error message when you connect to an instance of SQL Server 2008 Analysis Services by using the AMO library that is included with SQL Server 2005 Service Pack 2 Analysis Services: "Cannot connect to Analysis Services version '10.0.1019.17'"
|
-
This script will generate tsql code to bcp and bulk load data for all tables in a given DB. SET NOCOUNT ON GO DECLARE @path nvarchar(2000), @batchsize nvarchar(40), @format nvarchar(40), @serverinstance nvarchar(200), @security nvarchar(800) SET @path = 'C:\Temp\'; SET @batchsize = '1000000' -- COMMIT EVERY n RECORDS SET @serverinstance = 'PGALLUCC-M7' --SQL Server \ Instance name SET @security = ' -T ' -- -T (trusted), -Uloginid -Ploginpassword --GENERATE CONSTRAINT NO CHECK PRINT '--NO CHECK CONSTRAINTS' SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' NOCHECK CONSTRAINT ' + QUOTENAME( CONSTRAINT_NAME ) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS --DISABLE TRIGGERS PRINT '--DISABLE TRIGGERS' SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' DISABLE TRIGGER ALL' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' --BCP-OUT TABLES PRINT '--BCP OUT TABLES ' SELECT 'bcp "' + QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA ) + '.' + QUOTENAME( TABLE_NAME ) + '" out "' + @path + '' + TABLE_NAME + '.dat" -q -b"' + @batchsize + '" -e"' + @path + '' + TABLE_NAME + '.err" -n -CRAW -o"' + @path + '' + TABLE_NAME + '.out" -S"' + @serverinstance + '" ' + @security + '' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' -- CREATE NON-XML FORMAT FILE PRINT '--NON-XML FORMAT FILE' SELECT 'bcp "' + QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA ) + '.' + QUOTENAME( TABLE_NAME ) + '" format nul -n -CRAW -f "' + @path + '' + TABLE_NAME + '.fmt" --S"' + @serverinstance + '" ' + @security + '' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' -- CREATE XML FORMAT FILE PRINT '--XML FORMAT FILE' SELECT 'bcp "' +QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA ) + '.' + QUOTENAME( TABLE_NAME ) + '" format nul -x -n -CRAW -f "' + @path + '' + TABLE_NAME + '.xml" -S"' + @serverinstance + '" ' + @security + '' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' --TRUNCATE TABLE PRINT '--TRUNCATE TABLE' SELECT 'TRUNCATE TABLE ' +QUOTENAME( TABLE_NAME ) + ' GO ' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' --BULK INSERT PRINT '--BULK INSERT' SELECT DISTINCT 'BULK INSERT ' + QUOTENAME(TABLE_CATALOG) + '.' + QUOTENAME( TABLE_SCHEMA ) + '.' + QUOTENAME( TABLE_NAME ) + ' FROM ''' + @path + '' + TABLE_NAME + '.Dat'' WITH (FORMATFILE = ''' + @path + '' + TABLE_NAME + '.FMT'', BATCHSIZE = ' + @batchsize + ', ERRORFILE = ''' + @path + 'BI_' + TABLE_NAME + '.ERR'', TABLOCK); GO ' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' --OPENROWSET PRINT '--OPENROWSET' SELECT DISTINCT 'INSERT INTO ' + QUOTENAME(TABLE_CATALOG) + '.' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' SELECT * FROM OPENROWSET(BULK ''' + @path + '' + TABLE_NAME + '.Dat'', FORMATFILE=''' + @path + '' + TABLE_NAME + '.Xml'' ) as t1 ; GO ' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' --GENERATE CONSTRAINT CHECK CONSTRAINT TO VERIFY DATA AFTER LOAD PRINT '--CHECK CONSTRAINT' SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' CHECK CONSTRAINT ' + QUOTENAME( CONSTRAINT_NAME ) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS --ENABLE TRIGGERS PRINT '--ENABLE TRIGGERS' SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' ENABLE TRIGGER ALL' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
|
|
|
|
