In SQL Server, we can query the sys.default_constraints system catalog view to return a list of DEFAULT constraints in the current database.
Example
SELECT
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
COL_NAME(parent_object_id, parent_column_id) AS ColumnName,
name AS ConstraintName,
definition AS ConstraintDefinition
FROM sys.default_constraints;Example result:
SchemaName TableName ColumnName ConstraintName ConstraintDefinition ----------- --------------------- ---------------------- --------------------------------------------------------- ---------------------------------------------------- Warehouse Colors ColorID DF_Warehouse_Colors_ColorID (NEXT VALUE FOR [Sequences].[ColorID]) Sales Invoices LastEditedWhen DF_Sales_Invoices_LastEditedWhen (sysdatetime()) Sales OrderLines OrderLineID DF_Sales_OrderLines_OrderLineID (NEXT VALUE FOR [Sequences].[OrderLineID]) Warehouse PackageTypes PackageTypeID DF_Warehouse_PackageTypes_PackageTypeID (NEXT VALUE FOR [Sequences].[PackageTypeID]) Sales OrderLines LastEditedWhen DF_Sales_OrderLines_LastEditedWhen (sysdatetime()) Warehouse StockGroups StockGroupID DF_Warehouse_StockGroups_StockGroupID (NEXT VALUE FOR [Sequences].[StockGroupID]) Warehouse StockItemStockGroups StockItemStockGroupID DF_Warehouse_StockItemStockGroups_StockItemStockGroupID (NEXT VALUE FOR [Sequences].[StockItemStockGroupID]) Warehouse StockItemStockGroups LastEditedWhen DF_Warehouse_StockItemStockGroups_LastEditedWhen (sysdatetime()) Application StateProvinces StateProvinceID DF_Application_StateProvinces_StateProvinceID (NEXT VALUE FOR [Sequences].[StateProvinceID]) Sales CustomerTransactions CustomerTransactionID DF_Sales_CustomerTransactions_CustomerTransactionID (NEXT VALUE FOR [Sequences].[TransactionID]) Application Cities CityID DF_Application_Cities_CityID (NEXT VALUE FOR [Sequences].[CityID]) Sales CustomerTransactions LastEditedWhen DF_Sales_CustomerTransactions_LastEditedWhen (sysdatetime()) Application SystemParameters SystemParameterID DF_Application_SystemParameters_SystemParameterID (NEXT VALUE FOR [Sequences].[SystemParameterID]) Sales InvoiceLines InvoiceLineID DF_Sales_InvoiceLines_InvoiceLineID (NEXT VALUE FOR [Sequences].[InvoiceLineID]) Application SystemParameters LastEditedWhen DF_Application_SystemParameters_LastEditedWhen (sysdatetime()) Sales InvoiceLines LastEditedWhen DF_Sales_InvoiceLines_LastEditedWhen (sysdatetime()) Purchasing Suppliers SupplierID DF_Purchasing_Suppliers_SupplierID (NEXT VALUE FOR [Sequences].[SupplierID]) Warehouse StockItemTransactions StockItemTransactionID DF_Warehouse_StockItemTransactions_StockItemTransactionID (NEXT VALUE FOR [Sequences].[TransactionID]) Warehouse StockItemTransactions LastEditedWhen DF_Warehouse_StockItemTransactions_LastEditedWhen (sysdatetime()) Sales Customers CustomerID DF_Sales_Customers_CustomerID (NEXT VALUE FOR [Sequences].[CustomerID]) Purchasing PurchaseOrders PurchaseOrderID DF_Purchasing_PurchaseOrders_PurchaseOrderID (NEXT VALUE FOR [Sequences].[PurchaseOrderID]) Purchasing PurchaseOrders LastEditedWhen DF_Purchasing_PurchaseOrders_LastEditedWhen (sysdatetime()) Sales Orders OrderID DF_Sales_Orders_OrderID (NEXT VALUE FOR [Sequences].[OrderID]) Sales Orders LastEditedWhen DF_Sales_Orders_LastEditedWhen (sysdatetime()) SchemaName TableName ColumnName ConstraintName ConstraintDefinition ----------- -------------------- --------------------- -------------------------------------------------------- -------------------------------------------------- Application People PersonID DF_Application_People_PersonID (NEXT VALUE FOR [Sequences].[PersonID]) Warehouse StockItems StockItemID DF_Warehouse_StockItems_StockItemID (NEXT VALUE FOR [Sequences].[StockItemID]) Application Countries CountryID DF_Application_Countries_CountryID (NEXT VALUE FOR [Sequences].[CountryID]) Warehouse StockItemHoldings LastEditedWhen DF_Warehouse_StockItemHoldings_LastEditedWhen (sysdatetime()) Purchasing PurchaseOrderLines PurchaseOrderLineID DF_Purchasing_PurchaseOrderLines_PurchaseOrderLineID (NEXT VALUE FOR [Sequences].[PurchaseOrderLineID]) Application DeliveryMethods DeliveryMethodID DF_Application_DeliveryMethods_DeliveryMethodID (NEXT VALUE FOR [Sequences].[DeliveryMethodID]) Purchasing PurchaseOrderLines LastEditedWhen DF_Purchasing_PurchaseOrderLines_LastEditedWhen (sysdatetime()) Purchasing SupplierTransactions SupplierTransactionID DF_Purchasing_SupplierTransactions_SupplierTransactionID (NEXT VALUE FOR [Sequences].[TransactionID]) Application PaymentMethods PaymentMethodID DF_Application_PaymentMethods_PaymentMethodID (NEXT VALUE FOR [Sequences].[PaymentMethodID]) Purchasing SupplierTransactions LastEditedWhen DF_Purchasing_SupplierTransactions_LastEditedWhen (sysdatetime()) Application TransactionTypes TransactionTypeID DF_Application_TransactionTypes_TransactionTypeID (NEXT VALUE FOR [Sequences].[TransactionTypeID]) Sales SpecialDeals SpecialDealID DF_Sales_SpecialDeals_SpecialDealID (NEXT VALUE FOR [Sequences].[SpecialDealID]) Purchasing SupplierCategories SupplierCategoryID DF_Purchasing_SupplierCategories_SupplierCategoryID (NEXT VALUE FOR [Sequences].[SupplierCategoryID]) Sales SpecialDeals LastEditedWhen DF_Sales_SpecialDeals_LastEditedWhen (sysdatetime()) Sales BuyingGroups BuyingGroupID DF_Sales_BuyingGroups_BuyingGroupID (NEXT VALUE FOR [Sequences].[BuyingGroupID]) Sales Invoices InvoiceID DF_Sales_Invoices_InvoiceID (NEXT VALUE FOR [Sequences].[InvoiceID]) Sales CustomerCategories CustomerCategoryID DF_Sales_CustomerCategories_CustomerCategoryID (NEXT VALUE FOR [Sequences].[CustomerCategoryID]) 41 row(s) returned
I ran that against the WorldWideImporters sample database.
The view returns the IDs of each constraint’s schema, parent table, and parent column. I used SCHEMA_NAME() to get the schema name from its ID, OBJECT_NAME() to get the table name from its ID, and COL_NAME() to get the column name from its ID.