如何检查表存在，然后重命名它 [英] How to check table exist and then rename it

问题描述

你好编码器我陷入困境，我需要你的帮助.. PFB代码：

Hi coder I am stuck at a point and I need your help.. PFB code:

DECLARE @TableSchema sys.sysname = N'dbo';
DECLARE @TableName sys.sysname = N'x';
DECLARE @BackupTable sys.sysname = @TableName + '_' + CONVERT(VARCHAR(32), GETDATE(), 120);


DECLARE @SQL NVARCHAR(MAX) = N'

DECLARE @TableWithSchema NVARCHAR(256) = QUOTENAME(@TableSchema) + ''.'' + QUOTENAME(@TableName);

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_SCHEMA = @TableSchema
             AND  TABLE_NAME = @TableName))
BEGIN
  EXEC sp_rename @TableWithSchema, @BackupTable, ''OBJECT''
END

CREATE TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + '(
/* Column definitions here*/
);
';

EXEC sp_executesql
    @stmt = @SQL
  , @params = N'@TableSchema sys.sysname, @TableName sys.sysname, @BackupTable sys.sysname'
  , @TableSchema = @TableSchema
  , @TableName = @TableName
  , @BackupTable = @BackupTable
;
DECLARE @CMD nvarchar(8000)
 SET @CMD='BCP db..'+@TableName+' IN file path -T -f -t,-c -E'
 EXEC MASTER..XP_CMDSHELL @CMD

This code is working fine but in this code suppose I have no table with name X so I m doing it for the first time, when I run it for the first time it will create a table with name X, when I run it for the second time it should update the existing one with x_getdate() but it is creating new table how can I fix this

我尝试了什么：

What I have tried:

DECLARE @TableSchema sys.sysname = N'dbo';
DECLARE @TableName sys.sysname = N'x';
DECLARE @BackupTable sys.sysname = @TableName + '_' + CONVERT(VARCHAR(32), GETDATE(), 120);


DECLARE @SQL NVARCHAR(MAX) = N'

DECLARE @TableWithSchema NVARCHAR(256) = QUOTENAME(@TableSchema) + ''.'' + QUOTENAME(@TableName);

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_SCHEMA = @TableSchema
             AND  TABLE_NAME = @TableName))
BEGIN
  EXEC sp_rename @TableWithSchema, @BackupTable, ''OBJECT''
END

CREATE TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + '(
/* Column definitions here*/
);
';

EXEC sp_executesql
    @stmt = @SQL
  , @params = N'@TableSchema sys.sysname, @TableName sys.sysname, @BackupTable sys.sysname'
  , @TableSchema = @TableSchema
  , @TableName = @TableName
  , @BackupTable = @BackupTable
;
DECLARE @CMD nvarchar(8000)
 SET @CMD='BCP db..'+@TableName+' IN file path -T -f -t,-c -E'
 EXEC MASTER..XP_CMDSHELL @CMD

推荐答案

检查对于SQL中的表：

Check for table in SQL:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableName'

重命名表：

Rename table:

EXEC sp_rename 'TableName', 'NewTableName'

检查C＃中的表格：

Check for table in C#:

public static bool CheckTableExists(string connectionString, string tableName)
        {
            string query = string.Format(@"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}'", tableName);

            return ExecuteScalar(connectionString, query);
        }

这篇关于如何检查表存在，然后重命名它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！