SELECT OBJECT_NAME(C.OBJECT_ID) TableName, C.name ColumnName
FROM sys.columns AS C
JOIN sys.types AS T ON C.user_type_id=T.user_type_id
WHERE T.name = 'INT' --you can change to other datatypes
ORDER BY C.OBJECT_ID;
GO
SELECT OBJECT_NAME(C.OBJECT_ID) TableName, C.name ColumnName
FROM sys.columns AS C
JOIN sys.types AS T ON C.user_type_id=T.user_type_id
WHERE T.name = 'INT' --you can change to other datatypes
ORDER BY C.OBJECT_ID;
GO
SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < N
SELECT K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT ON PT.TABLE_NAME = PK.TABLE_NAME
SELECT CASE WHEN (GROUPING(sob.name)=1) THEN 'All_Tables'
ELSE ISNULL(sob.name, 'unknown') END AS Table_name,
SUM(sys.length) AS Byte_Length
FROM sysobjects sob, syscolumns sys
WHERE sob.xtype='u' AND sys.id=sob.id
GROUP BY sob.name
WITH CUBE
SELECT name AS FileName,
size*1.0/128 AS FileSizeinMB,
CASE max_size WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'
ELSE 'Log file will grow to a maximum size of 2 TB.'
END
AutogrowthStatus,
growth AS 'GrowthValue',
'GrowthIncrement' = CASE WHEN growth = 0 THEN 'Size is fixed and will not grow.'
WHEN growth > 0AND is_percent_growth = 0 THEN 'Growth value is in 8-KB pages.'
ELSE 'Growth value is a percentage.'END
FROM tempdb.sys.database_files;
GO
CREATE FUNCTION dbo.LTrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @trimchars VARCHAR(10) SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32) IF @str LIKE '[' + @trimchars + ']%' SET @str = SUBSTRING(@str, PATINDEX('%[^' + @trimchars + ']%', @str), 8000) RETURN @str END GO CREATE FUNCTION dbo.RTrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @trimchars VARCHAR(10) SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32) IF @str LIKE '%[' + @trimchars + ']' SET @str = REVERSE(dbo.LTrimX(REVERSE(@str))) RETURN @str END GO CREATE FUNCTION dbo.TrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN RETURN dbo.LTrimX(dbo.RTrimX(@str)) END GO /* Run the created function */ SELECT dbo.TRIMX(' word leading trailing spaces ') AS 'TrimmedWord' GO
Reference :SELECT OBJECT_NAME(OBJECT_ID) AS ConstraintName,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM SYS.OBJECTS
WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT')
GO
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads,
qs.last_logical_reads,
qs.total_logical_writes,
qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC
SELECT
[object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%objectname%'
AND [counter_name] = 'Page life expectancy'
CREATE TABLE #Table (C1 INT,C2 VARCHAR(10)) INSERT INTO #Table VALUES (1,'SQL Server') INSERT INTO #Table VALUES (1,'SQL Server') INSERT INTO #Table VALUES (2,'Oracle') SELECT * FROM #Table ;WITH Delete_Duplicate_Row_cte AS (SELECT ROW_NUMBER()OVER(PARTITION BY C1, C2 ORDER BY C1,C2) ROW_NUM,* FROM #Table ) DELETE FROM Delete_Duplicate_Row_cte WHERE ROW_NUM > 1 SELECT * FROM #Table --DROP TABLE #Table