SELECT [s].[name] [SchemaName], [t].[name] [TableName], [c].[name] [ColumnName], [tp].[name], CASE WHEN [tp].[name] IN (‘bigint’, ‘int’, ‘smallint’, ‘tinyint’, ‘real’, ‘float’, ‘bit’, ‘date’, ‘datetime’, ‘geography’, ‘geometry’, ‘hierarchyid’, ‘image’, ‘smallmoney’, ‘money’, ‘ntext’, ‘text’, ‘timestamp’, ‘smalldatetime’, ‘sql_variant’, ‘sysname’, ‘uniqueidentifier’, ‘xml’) THEN [tp].[name] WHEN [tp].[name] IN (‘nchar’, ‘nvarchar’, ‘varbinary’, ‘varchar’ ) AND [c].[max_length] = -1 THEN…
Primary Keys List
7. 10. 2021
This script is very useful in case we need to check if all our primary keys are also clustered. If not, then RID Lookup (Heap) will be used instead of Clustered Index Seek with all consequences related to it. SELECT [s].[name] [Schema_Name], [o].[name] AS [Object_Name], [o].[type_desc] [Object_Type], [ix].[name] AS [Primary_Key_Name],…
This query is extracted from the Disk Usage by Top Table report in SQL Server Management Studio. SELECT [a3].[name] AS [schemaname], [a2].[name] AS [tablename], [a1].[rows] AS [row_count], ( [a1].[reserved] + ISNULL ([a4].[reserved], 0)) * 8 AS [reserved], [a1].[data] * 8 AS [data], ( CASE WHEN ( [a1].[used] + ISNULL ([a4].[used],…
This scritp can be used to generate views to UNION ALL rows from all tables within two or more databases with the same schema. Replace $SCHEMA_SOURCE_DB$ with the name of the database you will take the schema from. All databases in the [db] list must have the same schema. Requires…
SELECT [t].[name] TableName, STRING_AGG([c].[name], ‘, ‘) WITHIN GROUP (ORDER BY [c].[column_id] ASC) [ColList] FROM [db.api-bd-slcr.yarmill.com].[sys].[tables] [t] INNER JOIN [db.api-bd-slcr.yarmill.com].[sys].[schemas] [s] ON [s].[schema_id] = [t].[schema_id] INNER JOIN [db.api-bd-slcr.yarmill.com].[sys].[columns] [c] ON [c].[object_id] = [t].[object_id] WHERE [s].[name] = ‘dbo’ AND [t].[name] <> ‘__RefactorLog’ GROUP BY [t].[name] GO
Check for name of an object in all databases
28. 3. 2019
With following script we can easy iterate all databases on instance and search inside routines (views, procedures, functions etc.) for name of some object i.e. in case we would like to rename linked server and we need to know where it is currently used. For sure we can use this…
List Foreign Key Relations
9. 3. 2018
SELECT [ro].[name] [PKtable], [rc].[name] [PKcolumn], [po].[name] [FKtable], [pc].[name] [FKcolumn], [fk].[name] [FKname] FROM [sys].[foreign_keys] [fk] INNER JOIN [sys].[all_objects] [po] ON [po].[object_id] = [fk].[parent_object_id] INNER JOIN [sys].[all_objects] [ro] ON [ro].[object_id] = [fk].[referenced_object_id] INNER JOIN [sys].[foreign_key_columns] [fkc] ON [fkc].[constraint_object_id] = [fk].[object_id] AND [fkc].[parent_object_id] = [fk].[parent_object_id] AND [fkc].[referenced_object_id] = [fk].[referenced_object_id] INNER JOIN [sys].[columns] [pc]…
Compressed Tables List
9. 3. 2018
SELECT [t].[name] [TableName], [i1].[name] [IndexName], [i1].[type_desc] [IndexType], [fg].[name] AS [FileGroupName], [p].[partition_number] AS [PartitionNumber], CAST([p].[rows] AS FLOAT) AS [RowCount], [p].[data_compression] AS [DataCompression], [p].[data_compression_desc] [DataCompressionDesc] FROM [sys].[tables] AS [t] INNER JOIN [sys].[indexes] AS [i1] ON [i1].[object_id] = [t].[object_id] INNER JOIN [sys].[partitions] AS [p] ON [p].[object_id]=CAST([t].[object_id] AS INT) AND [p].[index_id]=[i1].[index_id] INNER JOIN [sys].[indexes]…
SELECT SCHEMA_NAME([t].[schema_id]) [SchemaName], [t].[Name] [TableName], [c].[column_id] [ColumnId], [c].[name] [ColumnName], [tp].[name] [TypeName], [c].[max_length] [MaxLength], [c].[Precision], [c].[Scale], (SELECT COUNT(*) FROM [sys].[indexes] AS [i] INNER JOIN [sys].[index_columns] AS [ic] ON [i].[OBJECT_ID] = [ic].[OBJECT_ID] AND [i].[index_id] = [ic].[index_id] WHERE [i].[is_primary_key] = 1 AND [i].[object_id] = [t].[object_id] AND [ic].[column_id] = [c].[column_id]) [IsPK], [c].[is_identity] [IsIdentity] FROM…
SELECT CONCAT([s].[name],’.’,[t].[name]) [Table], [fk].[name] [ForeignKey], [c].[name] [Column], [dc].[name] [DefaultConstraint], [dc].[definition] [Default] FROM [sys].[foreign_key_columns] [fkc] INNER JOIN [sys].[foreign_keys] [fk] ON [fkc].[constraint_object_id] = [fk].[object_id] INNER JOIN [sys].[columns] [c] ON [c].[object_id] = [fkc].[parent_object_id] AND [c].[column_id] = [fkc].[parent_column_id] INNER JOIN [sys].[tables] [t] ON [t].[object_id] = [fkc].[parent_object_id] INNER JOIN [sys].[schemas] [s] ON [s].[schema_id] = [t].[schema_id]…