Insert data into a Text File

As you propably know that MS introduced in SQL Server 2005 a new command utility called SQLCMD. In this article I would like to show the difference between SQLCMD and "old fashioned" OSQL in terms of formating the text file's output.

If you run these two statements and look at the files , you'll see that SQL Server inserts three dotted lines in myoutput2.txt, not so good for reading. Opposite, in myoutput1.txt you see very good reading format.

EXEC master..xp_cmdshell 'SQLCMD -S URID\SQLSERVERDEV2005 -E -Q "SELECT TOP 10 * FROM pubs.dbo.authors" -b -o c:\myoutput1.txt', no_output
EXEC master..xp_cmdshell 'OSQL -S URID\SQLSERVERDEV2005 -E -Q "SELECT TOP 10 * FROM pubs.dbo.authors" -b -o c:\myoutput2.txt', no_output

Despite that SQL Server 2005 supports OSQL utility, I strongly recommend you using SQLCMD.

Disable all Foreign Keys

Recently I was told to clear up all user tables in the client's database SQL Server 2000 SP3.There are lots of contsrtaints and for this purpose we would like to disable them in order to delete all data. Try it on Northwind database before using on production. If you want to enable constraints ,simple replace NOCHECK with CHECK.

DECLARE @TableName nvarchar(257),
@ForeignKeyConstraintName sysname,
@SQLStatement nvarchar(4000)

DECLARE TableList CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS TableName,
QUOTENAME(CONSTRAINT_NAME) AS ForeignKeyConstraintName
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = 'FOREIGN KEY' AND
OBJECTPROPERTY
(
OBJECT_ID
(
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
),
'IsMSShipped') = 0

OPEN TableList
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TableList INTO
@TableName,
@ForeignKeyConstraintName
IF @@FETCH_STATUS = -1 BREAK
SET @SQLStatement =
N'ALTER TABLE ' +
@TableName +
N' NOCHECK CONSTRAINT ' +
@ForeignKeyConstraintName
RAISERROR (@SQLStatement, 0, 1) WITH NOWAIT
EXEC sp_executesql @SQLStatement
END
CLOSE TableList
DEALLOCATE TableList
GO

Getting an index's information

I was asked by one of the client to get all info about the indexes i.e name of the index , column's name, table's name etc. Please take a look at following script to return the data for SQL Server 2000.
use pubs

SELECT tbl = object_name(i.id), i.name as index_name, c.name as column_name,
isunique = indexproperty(i.id, i.name, 'IsUnique'),
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
constrtype = CASE o.type
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'UQ' THEN 'UNIQUE'
END
FROM sysindexes i
JOIN syscolumns c on i.id = c.id
JOIN sysindexkeys k on i.id = k.id
and i.indid = k.indid
and c.colid = k.colid
LEFT JOIN sysobjects o ON o.name = i.name
AND o.xtype in ('PK', 'UQ')
AND o.parent_obj = i.id
WHERE indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
AND objectproperty(i.id,'IsMSShipped')=0
ORDER BY tbl, i.name, k.keyno

For SQL Server 2005 I use the script written by Kalen Delaney.Remember there is new type of index called INCLUDE.

CREATE VIEW get_index_columns
AS
SELECT object_name(ic.object_id) as object_name , index_name = i.name,
'column' = c.name,
'column usage' = CASE ic.is_included_column
WHEN 0 then 'KEY'
ELSE 'INCLUDED'
END
FROM sys.index_columns ic JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
JOIN sys.indexes i
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id

After creating the view, you can select from it, and it will give you the
KEY columns and the INCLUDED columns in all the indexes in all the tables.
Or, you can add a WHERE clause for your own table or index:

SELECT * FROM get_index_columns
WHERE object_name = 'mytable'

Foreign Key dependency

Some time ago I was asked to truncate lots of tables in client's database.As such,all those tables have FK and PK dependency. You get the error like that if you run TRUNCATE TABLE Categories in Nortwind database as an example.
--Cannot truncate table 'Categories' because it is being referenced by a FOREIGN KEY constraint.

So , there is no such great problem as you know the database structure and can easily indetify parent-child tables. But what if you don't?
I'd like to show the function (sadly, I don't remember by whom it is written) that returns the tables ordered by its level. Level 0 means that it is refernced table and should be truncated last.

CREATE VIEW VFKs
AS

SELECT
FK.TABLE_SCHEMA AS child_table_schema,
FK.TABLE_NAME AS child_table_name,
PK.TABLE_SCHEMA AS parent_table_schema,
PK.TABLE_NAME AS parent_table_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON RC.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON RC.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
GO

CREATE FUNCTION dbo.fn_get_tree_of_tables()
RETURNS @tree TABLE
(
table_schema SYSNAME NOT NULL,
table_name SYSNAME NOT NULL,
lvl INT NOT NULL,
PRIMARY KEY(table_schema, table_name)
)
AS
BEGIN

DECLARE @lvl AS INT
SET @lvl = 0

-- top level tables
INSERT INTO @tree
SELECT DISTINCT
parent_table_schema,
parent_table_name,
@lvl
FROM VFKs AS P
WHERE NOT EXISTS
(SELECT *
FROM VFKs AS C
WHERE C.child_table_schema = P.parent_table_schema
AND C.child_table_name = P.parent_table_name)

WHILE @@rowcount > 0
BEGIN

SET @lvl = @lvl + 1

-- non top level tables
INSERT INTO @tree
SELECT DISTINCT
child_table_schema,
child_table_name,
@lvl
FROM VFKs AS C
JOIN @tree AS P
ON P.table_schema = C.parent_table_schema
AND P.table_name = C.parent_table_name
AND lvl = @lvl - 1
WHERE NOT EXISTS
(SELECT *
FROM @tree AS T
WHERE T.table_schema = C.child_table_schema
AND T.table_name = C.child_table_name)

END

SET @lvl = 0

-- tables with no fks
INSERT INTO @tree
SELECT TABLE_SCHEMA, TABLE_NAME, @lvl
FROM INFORMATION_SCHEMA.TABLES AS TB
WHERE NOT EXISTS(
SELECT *
FROM @tree AS TR
WHERE TB.TABLE_SCHEMA = TR.table_schema
AND TB.TABLE_NAME = TR.table_name)
AND
TB.TABLE_TYPE = 'BASE TABLE'

RETURN

END
GO

Here's the results of an invocation of the function in Northwind:

SELECT * FROM dbo.fn_get_tree_of_tables() AS T
ORDER BY lvl

table_schema table_name lvl
------------ ----------------------------------- ---
dbo Categories 0
dbo CustomerDemographics 0
dbo Customers 0
dbo Employees 0
dbo Region 0
dbo Shippers 0
dbo Suppliers 0
dbo Products 1
dbo Territories 1
dbo Orders 1
dbo CustomerCustomerDemo 1
dbo Order Details 2
dbo EmployeeTerritories 2

Object's modified date

There are lots of businesses have already upgraded to SQL Server 2005.At this time I'd like to take a look at great feature that we have in SQL Server 2005. I have been asked so many times how to know WHEN does the object change? It is a headache in SQL Server 2000. Fortunately, now we can answer that question.

Let's say the user changed number of stored procedures.Using the following statement we can easily get the data.

SELECT name,modify_date FROM sys.procedures
ORDER BY modify_date desc

But let's take it a little bit futher. I'd like to know when does user update the table last time?

CREATE TABLE dbo.uri(c INT NOT NULL PRIMARY KEY)
GO
INSERT INTOdbo.uri VALUES(10)
--we got one entry

SELECT object_name(object_id) AS name ,last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = db_id( 'demo' )

---try to change the data
UPDATE dbo.uri SET c =50 WHERE c =10

--you'll see that last_user_update is changed

SELECT object_name(object_id) AS name ,last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = db_id( 'demo' )

So ,you can play with these Dynamic Management Views to obtain more info.

Columns List of Views

One of myclient asked me nice question about getting columns list for views. Well, please take a look at following solution I came up with.

The first view just holds the data about view and their columns and as you can see it is not easy to read it at all.

CREATE VIEW v_getcolumns
AS
SELECT INFORMATION_SCHEMA.TABLES.TABLE_NAME ,COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES JOIN INFORMATION_SCHEMA.COLUMNS
ON INFORMATION_SCHEMA.TABLES.TABLE_NAME=INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA) + '.' +
QUOTENAME(INFORMATION_SCHEMA.TABLES.TABLE_NAME)
), 'IsMSShipped') = 0


The final step I build a crosstab to contacanate the names of the columns per view based on count.I ened upi with 10 columns per view as maximum number, you can modify it for your needs.The cool thing is if you specify '*' in SELECT statement within a view and running the following query you get all columns of the table.

SELECT TABLE_NAME ,MAX(CASE WHEN rnk = 1 then COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 2 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 3 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 4 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 5 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 6 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 7 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 8 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 9 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 10 THEN ', ' + COLUMN_NAME ELSE '' end) AS columns
FROM
(
SELECT A.TABLE_NAME ,A.COLUMN_NAME,COUNT(*)rnk
FROM v_getcolumns A, v_getcolumns B
WHERE A.TABLE_NAME=B.TABLE_NAME AND
A.COLUMN_NAME>=B.COLUMN_NAME
GROUP BY a.TABLE_NAME ,a.COLUMN_NAME
) AS der
GROUP BY TABLE_NAME

T-SQL and CLR types do not match

As you know in SQL Server 2005 you can reference to .NET classes doing some operations that in T-SQL is realy hard to perform. For example if you need to format very long and 'complicated' string value , so by using .NET String method you can easily achive it. But I'd like to show you another example actually posted by Itzik Ben-Gan on his website ,so I just modified his C# code to VB.NET that is invoking the ToString method of the SqlDateTime datatype with a format string.

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Public Class Valid
"<"SqlFunction(DataAccess:=DataAccessKind.Read, IsDeterministic:=True)">" _
Public Shared Function fn_format_datetime(ByVal dt As SqlTypes.SqlDateTime, _
ByVal formatstring As SqlTypes.SqlString) As String
If dt.IsNull Or formatstring.IsNull Then
Return formatstring.IsNull()
Else
Return dt.Value.ToString(formatstring.Value)
End If

End Function

End Class

I assume you are familiar with .NET technology , so I build a solution and try to reference to this function within my database.I created ASSEMBLY without problem but when I tried to create a function I got the error "CREATE FUNCTION for "fn_format_datetime1" failed because T-SQL and CLR types for return value do not match."

CREATE ASSEMBLY dateformat1 FROM 'C:\dateformat1 .dll'
with permission_set = SAFE
CREATE FUNCTION fn_format_datetime(@dt as datetime,
@formatstring as VARCHAR(100))RETURNS VARCHAR(100)
AS EXTERNAL NAME dateformat1.[dateformat1.classname].fn_format_datetime

As you see T-SQL datatype VARCHAR(n) does not match .NET STRING class. I should use NVARCHAR(n) datatype to get it working, did not know it.

CREATE ASSEMBLY dateformat1 FROM 'C:\dateformat1 .dll'
with permission_set = SAFE
CREATE FUNCTION fn_format_datetime(@dt as datetime,
@formatstring as NVARCHAR(4000))RETURNS NVARCHAR(4000)
AS EXTERNAL NAME dateformat1.[dateformat1.classname].fn_format_datetime

--Usage
SELECT
dbo.fn_format_datetime(GETDATE(), 'yyyyMMdd'),
dbo.fn_format_datetime(GETDATE(), 'yyyymmdd');