- INFORMATION_SCHEMA.TABLES
- sys.tables
- sys.objects
- sys.all_objects
- OBJECTPROPERTY ()
- OBJECTPROPERTYEX ()
Query
SELECT *
FROM sys.external_tables
|
My code library incase i need them. Some of them are not ORIGINAL but i hope the original author would not mind i share their idea. In-case that the original author felt any discomfort, kindly alert me to remove the post.
SELECT *
FROM sys.external_tables
|
DECLARE @Test TABLE(Col1 INT, Col2 INT)
INSERT INTO @Test VALUES (1,2),(2,NULL)
|
SELECT *
FROM @Test
WHERE Col2 <> 2
|
SELECT CASE WHEN 1 = NULL THEN 1
WHEN 1 <> NULL THEN 2
ELSE 3 END
| ![]() |
CREATE TABLE Test_Locking (Col1 int)
INSERT INTO Test_Locking VALUES (1),(2),(3),(4),(5)
|
SELECT COUNT(*) FROM Test_Locking
|
BEGIN TRANSACTION
INSERT INTO Test_Locking VALUES (6)
|
BEGIN TRANSACTION
UPDATE Test_Locking
SET Col1 = 6 WHERE Col1 = 1
|
BEGIN TRANSACTION
DELETE Test_Locking WHERE Col1 = 1
|
SELECT
Table_Name
= OBJECT_NAME(pdw_table_mappings.object_id)
, Table_Distribution
= CASE WHEN pdw_table_distribution_properties.distribution_policy_desc =
'HASH' THEN 'DISTRIBUTE' ELSE
pdw_table_distribution_properties.distribution_policy_desc
END
, Index_Type = indexes.type_desc
, PDW_Node = pdw_nodes_partitions.pdw_node_id
, Distribution = pdw_distributions.distribution_id
, Partition_Number
= pdw_nodes_partitions.partition_number
, Partition_Position
= CASE WHEN partition_functions.boundary_value_on_right =
1 THEN 'RIGHT' WHEN partition_functions.boundary_value_on_right =
0 THEN 'LEFT' END
, Partition_Value
= partition_range_values.value
, Rows = CASE WHEN indexes.type IN (0,1) THEN
pdw_nodes_partitions.rows
ELSE
pdw_nodes_column_store_row_groups.total_rows
END
, ColumnStore_State
= pdw_nodes_column_store_row_groups.state_description
FROM sys.pdw_table_mappings
LEFT JOIN sys.indexes
ON indexes.object_id = pdw_table_mappings.object_id
AND indexes.type <> 2
LEFT JOIN sys.pdw_table_distribution_properties
ON pdw_table_distribution_properties.object_id = pdw_table_mappings.object_id
LEFT JOIN sys.pdw_nodes_tables
ON pdw_nodes_tables.name
= pdw_table_mappings.physical_name
LEFT JOIN sys.pdw_distributions
ON ((pdw_table_distribution_properties.distribution_policy =
2 AND pdw_distributions.pdw_node_id
= pdw_nodes_tables.pdw_node_id) OR 1 = 2)
AND ((pdw_table_distribution_properties.distribution_policy =
2 AND pdw_distributions.name
= RIGHT(pdw_nodes_tables.name, 1)) OR 1 = 2)
LEFT JOIN ( SELECT object_id,
index_id, partition_number, pdw_node_id, rows = sum(rows)
FROM sys.pdw_nodes_partitions
GROUP BY object_id,
index_id, partition_number, pdw_node_id)pdw_nodes_partitions
ON pdw_nodes_partitions.object_id =
pdw_nodes_tables.object_id
AND pdw_nodes_partitions.pdw_node_id
= pdw_nodes_tables.pdw_node_id
AND pdw_nodes_partitions.index_id
= indexes.index_id
LEFT JOIN sys.partitions
ON partitions.object_id = indexes.object_id
AND partitions.index_id = indexes.index_id
AND partitions.partition_number =
pdw_nodes_partitions.partition_number
LEFT JOIN sys.partition_schemes
ON partition_schemes.data_space_id = indexes.data_space_id
LEFT JOIN sys.partition_functions
ON partition_functions.function_id = partition_schemes.function_id
LEFT JOIN sys.partition_range_values
ON partition_range_values.function_id = partition_functions.function_id
AND COALESCE(partition_range_values.boundary_id + 1, 1) = partitions.partition_number
LEFT JOIN ( SELECT object_id,
index_id, partition_number, state_description,
pdw_node_id, total_rows = SUM(total_rows)
FROM sys.pdw_nodes_column_store_row_groups
WHERE total_rows IS NOT NULL
GROUP BY object_id,
index_id, partition_number, state_description,
pdw_node_id)pdw_nodes_column_store_row_groups
ON pdw_nodes_column_store_row_groups.object_id = pdw_nodes_partitions.object_id
AND pdw_nodes_column_store_row_groups.index_id =
pdw_nodes_partitions.index_id
AND pdw_nodes_column_store_row_groups.partition_number =
pdw_nodes_partitions.partition_number
AND pdw_nodes_column_store_row_groups.pdw_node_id =
pdw_nodes_partitions.pdw_node_id
|
DECLARE @DateTime DATETIME
= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
SELECT
@DateTime
, DATEADD(HOUR, 1, @DateTime)
, @DateTime +
0.04166667
|
sp_msforeachdb '
DECLARE
@SearchText VARCHAR(MAX) = ''a''
DECLARE @SQL
VARCHAR(MAX) = ''SELECT [Database Name] = ''''?'''', [Schema Name] =
TABLESCHEMA, [Table Name] = TABLENAME, [Column Name] = COLUMNNAME, [Occur
Count] = SUM(CNT) FROM ('' + STUFF(
(SELECT '' UNION ALL SELECT TABLESCHEMA = '''''' + TABLES.TABLE_SCHEMA +
'''''', TABLENAME = '''''' + TABLES.TABLE_NAME + '''''', COLUMNNAME =
CAST('''''' + COLUMN_NAME + '''''' AS VARCHAR(MAX)), CNT = 1 FROM ?.['' +
TABLES.TABLE_SCHEMA + ''].['' + TABLES.TABLE_NAME + ''] WHERE '' + CASE WHEN
DATA_TYPE = ''image'' THEN ''CAST('' ELSE '''' END + ''CAST(['' + COLUMN_NAME +
'']'' + CASE WHEN DATA_TYPE = ''image'' THEN '' AS VARBINARY(MAX))'' ELSE ''''
END + '' AS VARCHAR(MAX)) LIKE ''''%'' + @SearchText + ''%''''''
FROM ?.INFORMATION_SCHEMA.TABLES
JOIN ?.INFORMATION_SCHEMA.COLUMNS
ON TABLES.TABLE_CATALOG =
COLUMNS.TABLE_CATALOG
AND TABLES.TABLE_SCHEMA =
COLUMNS.TABLE_SCHEMA
AND TABLES.TABLE_NAME =
COLUMNS.TABLE_NAME
WHERE TABLES.TABLE_TYPE = ''BASE TABLE'' AND TABLES.TABLE_CATALOG NOT IN
(''master'',''model'',''msdb'',''tempdb'')
FOR XML PATH(''''))
, 1, 11, '''') + '')Src GROUP BY
TABLESCHEMA, TABLENAME, COLUMNNAME''
EXEC (@SQL)'
|
SELECT 0.28 +
(SELECT ((ROUND((RandumNumber.RandumNumber*100)/3,0))/100) -
(ROUND((RandumNumber.RandumNumber*100)/25,0)/100)
FROM (SELECT (ABS(CHECKSUM(NEWID()))%100)/100.0 AS RandumNumber)RandumNumber)
GO 5
|
SELECT
job.name
, job.description
, jobstep.step_name
, [SSIS Path] =
folder.foldername +
'\' + package.name
FROM sysjobs job
JOIN sysjobsteps jobstep
ON job.job_id
= jobstep.job_id
AND jobstep.subsystem = 'SSIS'
AND (jobstep.command LIKE '%/SERVER ' + @@SERVERNAME + '%'
OR jobstep.command LIKE '%/SERVER
"(local)"%'
OR jobstep.command LIKE '%/SERVER
"."%')
JOIN sysssispackagefolders
folder
JOIN sysssispackages package
ON folder.folderid = package.folderid
ON jobstep.command LIKE '%' + folder.foldername + '\' + package.name + '%'
WHERE CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) LIKE '% TABLE NAME %'
and job.enabled = 1
|
DECLARE @startdate
DATETIME = '20130501', @enddate DATETIME = '20130531'
;WITH CTE AS(
SELECT @startdate AS Dates
UNION ALL
SELECT Dates + 1
FROM CTE
WHERE Dates < @enddate
)
SELECT
[Date Column] = Dates,
[English name of the day] = DATENAME(dw, Dates),
[Formula] = DATEDIFF(DAY, 0, Dates)%7
FROM CTE |
;WITH Mable AS(
SELECT 1 Mable
UNION ALL
SELECT Mable + 1
FROM Mable
WHERE Mable < 100
), GatesA AS(
SELECT
*, HopperA = CASE WHEN Mable % 3 = 0 THEN 1 ELSE 0 END
FROM Mable
), GatesB AS(
SELECT *, HopperB = ROW_NUMBER() OVER (ORDER BY Mable) % 4
FROM GatesA
WHERE HopperA <> 1)
SELECT
SUM(CASE WHEN A.HopperA = 1 THEN 1 ELSE 0 END)
, SUM(CASE WHEN B.HopperB = 0 THEN 1 ELSE 0 END)
, COUNT(1)
- SUM(CASE WHEN A.HopperA = 1 THEN 1 ELSE 0 END) - SUM(CASE WHEN B.HopperB = 0 THEN 1 ELSE 0 END)
FROM GatesA A
LEFT JOIN GatesB B
ON A.Mable = B.Mable
|
;WITH Marble AS(
SELECT 1 Marble
UNION ALL
SELECT Marble + 1
FROM Marble
WHERE Marble < 100
)
SELECT
SUM(CASE WHEN Marble % 3 = 0 THEN 1 ELSE 0 END)
, SUM(CASE WHEN (Marble + 1) % 6 = 0 THEN 1 ELSE 0 END)
, COUNT(1)
- SUM(CASE WHEN Marble % 3 = 0 THEN 1 ELSE 0 END)
- SUM(CASE WHEN (Marble + 1) % 6 = 0 THEN 1 ELSE 0 END)
FROM Marble
|
DECLARE @RandomNumber TABLE(Col1 int)
INSERT INTO @RandomNumber VALUES (102),(523),(413),(9),(126),(83091),(36)
|
DECLARE @n INT = 3
SELECT
*
, AssignedNumber = (ROW_NUMBER() OVER (ORDER BY Col1) - 1) % @n + 1
FROM @RandomNumber
|