How to drop all tables, all views, and all stored procedures from a SQL 2005 DB…

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)…

6

SQLCMD and the power of the little r

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…

7

SQL Server Data Types Test Tables

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>…

1

Column Information View for SQL Server

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…

4

Generate BCP, Bulk Insert code with TSQL

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…

5