I found a very handy SQL script on this post, that allows you to enable or disable all constraints on a database. The only problem I had was that my database uses schemas to organize the tables, so the script failed to work.
I made a modification on the script to solve the problem, so here it is:
/************************** DISABLE ALL TABLE CONSTRAINTS **********************************
This script will enable or disable all constraints on all tables within the database
that it is run in.
********************************************************************************************/
SET NOCOUNT ON
SET ROWCOUNT 0
DECLARE @COUNT INT
DECLARE @String nvarchar (1000)
DECLARE @ConstraintName VARCHAR(128)
DECLARE @TableName VARCHAR(128)
DECLARE @SchemaName VARCHAR(128)
--Find all constraints and their respective tables from the sysobjects table and place into a temp table.
--Primary Key and Unique Constraints via Unique Indexes are not disabled through this command
--You should use the ALTER INDEX...DISABLE command in SQL Server 2005
SELECT
o.name AS ConstraintName,
sc.name AS SchemaName,
object_name(o.parent_obj) AS TableName
INTO #Const_Table
FROM sysobjects o, sys.schemas sc
WHERE xtype IN ('F') AND o.uid = sc.schema_id
SELECT @COUNT = COUNT(*) FROM #Const_Table
--Setting the rowcount to one allows for one row from the temp table to be picked off at a time.
--Used as an alternative to a cursor.
SET ROWCOUNT 1
--Loop until all rows in temp table have been processed.
WHILE @COUNT > 0
BEGIN
--The rowcount of one ensures that only one tablename and constraint name is picked.
SELECT @TableName = TableName, @SchemaName = SchemaName, @ConstraintName = ConstraintName
FROM #Const_Table
--Comment or uncomment the following statements according to your needs
--Build execution string to enable constraint.
SET @String = 'ALTER TABLE ['+ @SchemaName + '].['+ @TableName + '] WITH CHECK CHECK CONSTRAINT [' + @ConstraintName +']'
--Build execution string to disable constraint.
--SET @String = 'ALTER TABLE ['+ @SchemaName + '].['+ @TableName + '] NOCHECK CONSTRAINT [' + @ConstraintName +']'
--Execute the SQL
EXEC sp_executesql @string
--Remove this row from the temp table, since it has now been processed.
DELETE FROM #Const_Table
WHERE ConstraintName = @ConstraintName AND TableName = @TableName AND SchemaName = @SchemaName
SET @COUNT = @COUNT - 1
END -- Loop
DROP TABLE #Const_Table
SET ROWCOUNT 0 |
/************************** DISABLE ALL TABLE CONSTRAINTS **********************************
This script will enable or disable all constraints on all tables within the database
that it is run in.
********************************************************************************************/
SET NOCOUNT ON
SET ROWCOUNT 0
DECLARE @Count int
DECLARE @String nvarchar (1000)
DECLARE @ConstraintName varchar(128)
DECLARE @TableName varchar(128)
DECLARE @SchemaName varchar(128)
--Find all constraints and their respective tables from the sysobjects table and place into a temp table.
--Primary Key and Unique Constraints via Unique Indexes are not disabled through this command
--You should use the ALTER INDEX...DISABLE command in SQL Server 2005
SELECT
o.name AS ConstraintName,
sc.name AS SchemaName,
object_name(o.parent_obj) AS TableName
INTO #Const_Table
FROM sysobjects o, sys.schemas sc
WHERE xtype in ('F') AND o.uid = sc.schema_id
SELECT @Count = Count(*) FROM #Const_Table
--Setting the rowcount to one allows for one row from the temp table to be picked off at a time.
--Used as an alternative to a cursor.
SET ROWCOUNT 1
--Loop until all rows in temp table have been processed.
WHILE @Count > 0
BEGIN
--The rowcount of one ensures that only one tablename and constraint name is picked.
SELECT @TableName = TableName, @SchemaName = SchemaName, @ConstraintName = ConstraintName
FROM #Const_Table
--Comment or uncomment the following statements according to your needs
--Build execution string to enable constraint.
SET @String = 'ALTER TABLE ['+ @SchemaName + '].['+ @TableName + '] WITH CHECK CHECK CONSTRAINT [' + @ConstraintName +']'
--Build execution string to disable constraint.
--SET @String = 'ALTER TABLE ['+ @SchemaName + '].['+ @TableName + '] NOCHECK CONSTRAINT [' + @ConstraintName +']'
--Execute the SQL
EXEC sp_executesql @string
--Remove this row from the temp table, since it has now been processed.
DELETE FROM #Const_Table
WHERE ConstraintName = @ConstraintName AND TableName = @TableName AND SchemaName = @SchemaName
SET @Count = @Count - 1
END -- Loop
DROP TABLE #Const_Table
SET ROWCOUNT 0
Para determinar cuál es la versión exacta de un servidor SQL Server (ya sea 2000, 2005 ó 2008), basta con ejecutar la siguiente consulta:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY('productlevel'), SERVERPROPERTY('edition') |
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY('productlevel'), SERVERPROPERTY('edition')
En mi caso, la consulta me devuelve algo como esto:
9.00.3080.00 |
SP2 |
Developer Edition |
Para versiones anteriores de SQL Server, podemos utilizar esta otra consulta:
Vía: Microsoft Support
posts.forEach(post => document.write(post))