会python真的可以为所欲为0(回)
350天前
这里还有人吗1(回)
535天前这里还有人吗0(回)
535天前
每天面对着电脑屏幕，敲打键盘。我所面对的并不只是代码，而是一种生活方式。0(回)
760天前到处都是羊，不想上班0(回)
840天前
鸽子
0(回)
844天前DECLARE @keyDateTimeValue DATETIME = '1/1/2015 12:00:00 AM' |
|
DECLARE @Results AS TABLE ( |
ColumnName NVARCHAR(370), |
ColumnValue NVARCHAR(3630) |
) |
|
SET NOCOUNT ON |
|
DECLARE @TableName NVARCHAR(256), |
@TableName2 NVARCHAR(258), |
@DateTimeColumnName NVARCHAR(128), |
@DateTimeColumnName2 NVARCHAR(130), |
@DateColumnName NVARCHAR(128), |
@DateColumnName2 NVARCHAR(130), |
@TimeColumnName NVARCHAR(128), |
@TimeColumnName2 NVARCHAR(130), |
@SearchStr2 NVARCHAR(110) |
|
SET @TableName = '' |
|
WHILE @TableName IS NOT NULL |
BEGIN |
SET @DateTimeColumnName = '' |
SET @DateColumnName = '' |
SET @TimeColumnName = '' |
SET @TableName = ( |
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) |
FROM INFORMATION_SCHEMA.TABLES |
WHERE TABLE_TYPE = 'BASE TABLE' |
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName |
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 |
) |
|
WHILE (@TableName IS NOT NULL) |
AND ( |
@DateTimeColumnName IS NOT NULL |
OR @DateColumnName IS NOT NULL |
OR @TimeColumnName IS NOT NULL |
) |
BEGIN |
SET @DateTimeColumnName = ( |
SELECT MIN(QUOTENAME(COLUMN_NAME)) |
FROM INFORMATION_SCHEMA.COLUMNS |
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) |
AND TABLE_NAME = PARSENAME(@TableName, 1) |
AND DATA_TYPE IN ( |
'datetime', |
'datetime2', |
'smalldatetime', |
'datetimeoffset', |
'timestamp' |
) |
AND QUOTENAME(COLUMN_NAME) > @DateTimeColumnName |
) |
SET @DateColumnName = ( |
SELECT MIN(QUOTENAME(COLUMN_NAME)) |
FROM INFORMATION_SCHEMA.COLUMNS |
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) |
AND TABLE_NAME = PARSENAME(@TableName, 1) |
AND DATA_TYPE = 'date' |
AND QUOTENAME(COLUMN_NAME) > @DateColumnName |
) |
SET @TimeColumnName = ( |
SELECT MIN(QUOTENAME(COLUMN_NAME)) |
FROM INFORMATION_SCHEMA.COLUMNS |
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) |
AND TABLE_NAME = PARSENAME(@TableName, 1) |
AND DATA_TYPE = 'time' |
AND QUOTENAME(COLUMN_NAME) > @TimeColumnName |
) |
SET @TableName2 = REPLACE(@TableName, '''', '''''') |
|
IF @DateTimeColumnName IS NOT NULL |
BEGIN |
SET @DateTimeColumnName2 = REPLACE(@DateTimeColumnName, '''', '''''') |
|
DECLARE @dt1 AS NVARCHAR(30) = CONVERT(NVARCHAR, @keyDateTimeValue, 121) |
|
INSERT INTO @Results |
EXEC ( |
'IF EXISTS(SELECT TOP 1 1 FROM ' + @TableName + ' (NOLOCK) WHERE ' + @DateTimeColumnName + ' = ''' + @dt1 + ''') SELECT DISTINCT ''' + @TableName2 + '.' + @DateTimeColumnName2 + ''', LEFT(' + @DateTimeColumnName + ', 3630) |
FROM ' + @TableName + ' (NOLOCK) WHERE ' + @DateTimeColumnName + ' = ''' + @dt1 + '''' |
) |
END |
|
IF @DateColumnName IS NOT NULL |
BEGIN |
SET @DateColumnName2 = REPLACE(@DateColumnName, '''', '''''') |
|
DECLARE @dt2 AS NVARCHAR(20) = CONVERT(NVARCHAR, @keyDateTimeValue, 111) |
|
INSERT INTO @Results |
EXEC ( |
'IF EXISTS(SELECT TOP 1 1 FROM ' + @TableName + ' (NOLOCK) WHERE ' + @DateColumnName + ' = ''' + @dt2 + ''') SELECT DISTINCT ''' + @TableName2 + '.' + @DateColumnName2 + ''', LEFT(' + @DateColumnName + ', 3630) |
FROM ' + @TableName + ' (NOLOCK) WHERE ' + @DateColumnName + ' = ''' + @dt2 + '''' |
) |
END |
|
IF @TimeColumnName IS NOT NULL |
BEGIN |
SET @TimeColumnName2 = REPLACE(@TimeColumnName, '''', '''''') |
|
DECLARE @dt3 AS NVARCHAR(20) = CONVERT(NVARCHAR, @keyDateTimeValue, 114) |
|
INSERT INTO @Results |
EXEC ( |
'IF EXISTS(SELECT TOP 1 1 FROM ' + @TableName + ' (NOLOCK) WHERE ' + @TimeColumnName + ' = ''' + @dt3 + ''') SELECT DISTINCT ''' + @TableName2 + '.' + @TimeColumnName2 + ''', LEFT(' + @TimeColumnName + ', 3630) |
FROM ' + @TableName + ' (NOLOCK) WHERE ' + @TimeColumnName + ' = ''' + @dt3 + '''' |
) |
END |
END |
END |
|
SELECT ColumnName, |
DATA_TYPE + CASE |
WHEN [CHARACTER_MAXIMUM_LENGTH] IS NULL |
THEN CASE |
WHEN [DATA_TYPE] = 'decimal' |
THEN '(' + CAST([NUMERIC_PRECISION] AS VARCHAR) + ',' + CAST([NUMERIC_SCALE] AS VARCHAR) + ')' |
ELSE '' |
END |
ELSE '(' + CASE |
WHEN [CHARACTER_MAXIMUM_LENGTH] = - 1 |
THEN 'MAX' |
ELSE CAST([CHARACTER_MAXIMUM_LENGTH] AS VARCHAR) |
END + ')' |
END [ColumnType], |
ColumnValue |
FROM @Results R |
INNER JOIN INFORMATION_SCHEMA.COLUMNS C |
ON QUOTENAME(C.[TABLE_SCHEMA]) + '.' + QUOTENAME(C.[TABLE_NAME]) + '.' + QUOTENAME(C.[COLUMN_NAME]) = ColumnName |
ORDER BY ColumnName |
