I Want to return All table names from a use data base but this just return a char
declare @contador int
set @contador = 1
while (@contador<=(select count(table_name) from INFORMATION_SCHEMA.TABLES))
begin
declare @tableName varchar
set @tableName = (select top 1 * from (select top(@contador) table_name from INFORMATION_SCHEMA.TABLES order by table_name asc) as nombre order by table_name desc)
print @tableName
set @contador = @contador + 1
end
the output is s s s s s s
declare @tableName varchar(100)
You need to define the length of @tableName, by default it is set to 1 character.
NVARCHAR(128) instead of VARCHAR(128) unless you are sure you will never have a table with a name outside your current code page. But if you see DDL like CREATE TABLE 数据(符合 INT);, you'll need to use NVARCHAR(128).try
declare @tableName varchar(100)
You need to change your tablename to have a value for the number of characters. Currently that value is defaulted to one. I would suggest a much larger value than you think you neeed to ensure that all tables fit inside the field.
declare @tableName varchar needs to have a size like varchar(50)
T-SQL has the type SYSNAME for storing things like table names:
The sysname data type is used for table columns, variables, and stored procedure parameters that store object names. The exact definition of sysname is related to the rules for identifiers. Therefore, it can vary between instances of SQL Server. sysname is functionally the same as nvarchar(128) except that, by default, sysname is NOT NULL. In earlier versions of SQL Server, sysname is defined as varchar(30).
So try declaring your variable like this:
DECLARE @tableName SYSNAME;
Using the VARCHAR(100) declaration, as suggested in other answers, will fail if the table name contains characters outside your current code page or is longer than 100 characters.
This excerpt from SQL Server's rules for identifiers describes the form of a table name:
The first character must be one of the following:
A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages.
The underscore (_), at sign (@), or number sign (#).
Certain symbols at the beginning of an identifier have special meaning in SQL Server. A regular identifier that starts with the at sign always denotes a local variable or parameter and cannot be used as the name of any other type of object. An identifier that starts with a number sign denotes a temporary table or procedure. An identifier that starts with double number signs (##) denotes a global temporary object. Although the number sign or double number sign characters can be used to begin the names of other types of objects, we do not recommend this practice.
Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, you should not use names that start with @@.
Subsequent characters can include the following:
Letters as defined in the Unicode Standard 3.2.
Decimal numbers from either Basic Latin or other national scripts.
The at sign, dollar sign ($), number sign, or underscore.
The identifier must not be a Transact-SQL reserved word. SQL Server reserves both the uppercase and lowercase versions of reserved words.
Embedded spaces or special characters are not allowed.
Supplementary characters are not allowed.
See the documentation links in my answer for more information.
select top 2 table_name from INFORMATION_SCHEMA.TABLES order by table_name ascnot good enough?PRINTa result set. You might want to print a table name rather than select it if you are writing a script that produces another script as output.