Let's say the new default you want is 5
:
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'ALTER TABLE ' + t + ' DROP CONSTRAINT ' + c + ';
ALTER TABLE ' + t + ' ADD CONSTRAINT ' + c
+ ' DEFAULT (5) FOR [project_id];'
FROM
(
SELECT t = QUOTENAME(s.name) + '.' + QUOTENAME(t.name),
c = QUOTENAME(d.name)
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
INNER JOIN sys.default_constraints AS d
ON d.parent_object_id = t.[object_id]
AND d.parent_column_id = c.column_id
WHERE c.name = N'project_id'
) AS x;
PRINT @sql;
--EXEC sp_executesql @sql;
Notes:
print
is limited to 8K, so if you have a lot of tables with this column, you might not actually see the entire command output in SSMS, and it may look truncated.
- This will only add the constraint to
project_id
columns that already have a default constraint. If you have other tables that have this column but don't currently have a constraint, you'll need a separate query.
- This will not change the values already generated based on the old defaults.
Here is why I recommend against INFORMATION_SCHEMA for this type of problem:
Try the following query. It first creates a local temporary table and then populates it with the AllocationUnitID-to-FileID associations found in sys.dm_db_database_page_allocations
, an undocumented Dynamic Management Function (DMF) introduced in SQL Server 2012 (for versions prior to 2012, you can get this info from DBCC IND()
). That local temp table is then JOINed into a modified version of the original query.
The data from that DMF is placed into a temporary table for performance since, depending on the size of the database, it could take more than a few seconds to get that data. The DISTINCT
keyword is used because that DMF returns one row per data page, and there are multiple data pages per each allocation unit.
I left-JOINed that data into the original query since the original query returns allocation units that have 0 data pages (typically ROW_OVERFLOW_DATA
and LOB_DATA
types). I also added the total_pages
field so that it would be easier to relate that data point to the rows that have NULL
s for the Data Files. If you don't care about the Allocation Units that have 0 rows, then it would be fine to change that LEFT JOIN
to be an INNER JOIN
.
IF (OBJECT_ID(N'tempdb..#AllocationsToFiles') IS NULL)
BEGIN
-- DROP TABLE #AllocationsToFiles;
CREATE TABLE #AllocationsToFiles
(
ObjectID INT NOT NULL,
IndexID INT NOT NULL,
PartitionID INT NOT NULL,
RowsetID BIGINT NOT NULL,
AllocationUnitID BIGINT NOT NULL,
AllocatedPageFileID SMALLINT NOT NULL
);
END;
IF (NOT EXISTS(SELECT * FROM #AllocationsToFiles))
BEGIN
--TRUNCATE TABLE #AllocationsToFiles;
INSERT INTO #AllocationsToFiles (ObjectID, IndexID, PartitionID, RowsetID,
AllocationUnitID, AllocatedPageFileID)
SELECT DISTINCT alloc.[object_id], alloc.[index_id], alloc.[partition_id],
alloc.[rowset_id], alloc.[allocation_unit_id], alloc.[allocated_page_file_id]
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL,
'LIMITED') alloc
WHERE alloc.is_allocated = 1
AND alloc.is_iam_page = 0;
END;
SELECT
SchemaName = sh.name,
TableName = t.name,
IndexName = i.name,
PartitionNumber = p.partition_number,
IndexID = i.index_id,
IndexDataspaceID = i.data_space_id,
AllocUnitDataspaceID = au.data_space_id,
PartitionRows = p.[rows],
TotalPages = au.total_pages,
AllocationUnitType = au.type_desc,
LogicalFileName = dbf.[name],
PhysicalFileName = dbf.[physical_name]
--,p.[object_id], p.[partition_id], au.allocation_unit_id
FROM sys.allocation_units au
INNER JOIN sys.partitions p
ON au.container_id = IIF(au.[type] = 2, p.[partition_id], p.[hobt_id])
INNER JOIN sys.indexes i
ON i.[object_id] = p.[object_id]
AND i.index_id = p.index_id
INNER JOIN sys.tables t
ON p.[object_id] = t.[object_id]
INNER JOIN sys.schemas sh
ON t.[schema_id] = sh.[schema_id]
LEFT JOIN (#AllocationsToFiles alloc
INNER JOIN sys.database_files dbf
ON dbf.[file_id] = alloc.AllocatedPageFileID
)
ON alloc.ObjectID = p.[object_id]
AND alloc.IndexID = p.index_id
AND alloc.PartitionID = p.partition_number
AND alloc.AllocationUnitID = au.allocation_unit_id
WHERE sh.name <> N'sys'
ORDER BY t.name, i.index_id, p.partition_number;
Best Answer
You can use INFORMATION_SCHEMA.COLUMNS instead of sys.columns