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'
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'
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
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')
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
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).
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
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
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;
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.
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.