Tag Archives: Data definition language

List all stored procedures in a database

~

This script will provide a list of all stored procedures in a database along with the create script for each.

SELECT specific_name, routine_definition 
  FROM information_schema.routines 
 WHERE routine_type = 'PROCEDURE'
[tweetmeme only_single=”false”]

Get list of columns in every table

~

Here is a script to get a list of columns for each table.

SELECT t.name AS TABLE_NAME, c.name AS COLUMN_NAME, c.column_id AS COLUMN_ORDER, c.system_type_id AS DATA_TYPE, c.max_length, c.precision, c.scale, c.is_nullable, c.is_identity 
FROM sys.columns AS c 
INNER JOIN sys.tables AS t ON c.object_id = t.object_id
[tweetmeme only_single=”false”]

List all Primary Keys in a database

~

This script will list all primary keys in a database.

SELECT ST.name AS TABLE_NAME, SKC.name AS CONSTRAINT_NAME, SC.name AS CONSTRAINT_COLUMN_NAME, SC.column_id AS COLUMN_ORDER 
FROM sys.key_constraints AS SKC 
INNER JOIN sys.tables AS ST ON ST.object_id = SKC.parent_object_id 
INNER JOIN sys.index_columns AS SIC ON SIC.object_id = ST.object_id AND SIC.index_id = SKC.unique_index_id 
INNER JOIN sys.columns AS SC ON SC.object_id = ST.object_id AND SC.column_id = SIC.column_id WHERE (LEFT(ST.name, 1) = N't') AND (ST.type = N'U')
[tweetmeme only_single=”false”]

How to find out what triggers you have.

~

I try to avoid using triggers because I always forget they are there.  I have been burned more than once by triggers updating data when I didn’t know they were there.  I have also been troubleshooting someone else’s database and been bitten by triggers.
The code from this post I copied from Chris McGowan’s blog.  He wrote up a great article about trigger status.  His post has code to create a job to email you.
I modified it to just run in SSMS and use a table variable (in case you don’t have the permissions to create a temp table).

This script runs against master and will tell you about all triggers in all databases.

Use master

-- Create table variable
DECLARE @TriggerStatus as Table
(
      DatabaseName SYSNAME,
      TableName VARCHAR(255), 
      TriggerName VARCHAR(255),
      TriggerStatus VARCHAR(8)
);
-- Insert triggers
INSERT INTO @TriggerStatus
EXEC sp_msforeachdb
'
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'', ''reportserver'', ''reportservertempdb'')
BEGIN
USE [?]; 
SELECT  DB_NAME() AS DatabaseName,
        OBJECT_NAME(parent_id) AS TableName,
        name AS TriggerName,
        CASE is_disabled
          WHEN 0 THEN ''Enabled''
          ELSE ''Disabled''
        END AS TriggerStatus
FROM    sys.triggers WITH ( NOLOCK )
WHERE   is_ms_shipped = 0
        AND parent_class = 1;
END'

SELECT DatabaseName, TableName, TriggerName,  TriggerStatus FROM   @TriggerStatus
[tweetmeme only_single=”false”]

Simple command to disable all Foreign Key checks

~

After blogging about how to disable all Foreign Key constraints and the undocumented sp for looping through all tables in a database, I thought about combining the two.

So I’ve now got a much easier way to disable FK constraints in a database.
The script below will loop through each table and disable the FK check.

--Disable FK Checks
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

I’ve got an application that has a SQL CE offline mode where the Foreign Key keyword is not supported when running scripts against it.  (At least that’s what I’ve been told by the original developer).

[tweetmeme only_single=”false”]

How to disable all Foreign Key constraints

~

Here is a script that will loop through all tables in your database and disable the foreign key constraints.

DECLARE @TableName nvarchar(256)
SET @TableName = ''

WHILE @TableName IS NOT NULL
BEGIN
SET @TableName =(SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped' ) = 0)

    IF (@TableName <> '')
    BEGIN
        EXEC ('ALTER TABLE '+ @TableName+ ' NOCHECK CONSTRAINT ALL;')
        PRINT ('ALTER TABLE '+ @TableName+ ' NOCHECK CONSTRAINT ALL;')
    END

END
GO
[tweetmeme only_single=”false”]

What little I know about SQL Bulk Insert

~

I’ve got a stored procedure to upload a csv file to a database table.  From what I’ve read that’s the fastest way to load csv data to SQL.

Cool, but I’ve had errors when I went to deploy to production.

Here are just a few things I’ve learned.
1. The permissions used are different when using windows login vs sql login (to call the stored procedure).
2. If you use a windows login to call the stored procedure it will use the permissions of the user to read the file (no matter what you what the SQL process is set to run).
3. If you use a SQL login then account the SQL process is setup to run as is used.
4.  Bulk Insert doesn’t like to pull data from any server,  but the server that SQL is running on.  If you do try pulling data from another server then make sure you use a UNC path.

After all that I still had permissions trouble.
So my application copied the csv file to a directory on the SQL server.  Then calling the procedure to do the Bulk Insert worked without issue.

Below is my stored procedure.  You just need to pass it the path to the file you want to import.
It will load it to a table dbtemp.
Make sure that table has the same fields as your source file.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_ImportFile] 
    @filename varchar(1000) 

AS
BEGIN

    SET NOCOUNT ON;

declare @sql varchar(4000)
select @sql = '
BULK
INSERT dbtemp
FROM ''' + @filename + '''
WITH
(
FIELDTERMINATOR = ''","'',
FIRSTROW = 2,
ROWTERMINATOR = ''\n''
)'
exec (@sql)
update dbtemp set funding = REPLACE(funding,'"',''), initialdate = REPLACE(initialdate,'"','')

END
[tweetmeme only_single=”false”]

How to insert records to a table with an identity column

~

If you have a table with an identity column, you can’t just do a simple insert.  That identity column needs to be excluded from your insert statement.

There are times when you need to keep the ID values during your insert.  Like moving data between environments.

Here is the SQL you need to allow your inserts to work.
First turn on identity_insert

SET IDENTITY_INSERT <TABLENAME> ON;

Then do your insert.

Then turn off identity_insert

SET IDENTITY_INSERT <TABLENAME> OFF;
[tweetmeme only_single=”false”]

Enable/Disable table indexes

~

Sometimes you need to disable indexes (and then re-enable them).
Here is the sql to disable indexes on a table.

ALTER INDEX ALL ON <TABLENAME> DISABLE;

Here is the sql to enable indexes on a table.

--Reenable indexes 
ALTER INDEX ALL ON <TABLENAME> REBUILD ;

Notice that to enable the indexes you just need to do a rebuild.

[tweetmeme only_single=”false”]

Undocumented stored procedure for looping through all databases on a server

~

If need to do something to all your databases there is a stored proc for you.  Its called sp_msforeachdb.
This will loop though all your databases and perform a command for you.  It’s similar to the foreach table sp I wrote about here.
There is one parameter @command.

Let’s get right to an example.

EXECUTE sp_msforeachdb ‘USE ? IF DB_NAME() NOT IN(”master”,”msdb”,”tempdb”,”model”) BACKUP DATABASE ? TO DISK = ”G:?.bak, WITH INIT”’

The ? gets replaced with the database name.
So that will backup any user database to the the G: drive.

This can simplify any looping code you have.

[tweetmeme only_single=”false”]