Just a day ago, I was looking for script which generates all the tables in database along with its schema name. I tried to Search@SQLAuthority.com but got too many results. For the same reason, I am going to write down today’s quick and small blog post and I will remember that I had written I wrote it after my 1000th article.
SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables
Reference :Â Pinal Dave (https://blog.sqlauthority.com)
32 Comments. Leave new
Hi Pinal,
Thanks for the blog. I have a question. I would like to get the table name and locks for that particular table. The current locks by a particular user can be found out in sys.dm_exec_sessions
Do you have any suggests?
Please let me know.
Thanks
Murali.
This will work too…
SELECT SCHEMA_NAME,NAME
FROM INFORMATION_SCHEMA.SCHEMATA i
INNER JOIN SYS.TABLES s
ON i.SCHEMA_NAME = SCHEMA_NAME(s.SCHEMA_ID)
ORDER BY SCHEMA_NAME
select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.COLUMNS
The above statements will give the same information using single statements
select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.COLUMNS
Hi,
I have created a linked server named like Test. It contains around 20 tables.
I want to list out tables in linked server in sql server. Can you please let me know how to do?
Thanks,
Murali
I am running something similar (see below). I use this to help me find what tables a field is in, however I noticed that a field/table combo that exists in the database does NOT appear when I run this, why is that? I also just looked for the table in sys.table and it doesn’t appear, it is a custom table, could that be the issue, do you have to run something to get tables to appear in sys.table?
SELECT
t.name AS table_name
,SCHEMA_NAME(schema_id) AS schema_name
,c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘insert field name’
ORDER BY schema_name, table_name;
Thanks everyone!
Jill
Is there any way i can know who created Schema, in my Db someone created Custom schema and I want to know who did that >>
Hello, is there any way to select all tables from all databases inside the same server?
Create table #yourcolumndetails(
DBaseName varchar(100),
TableSchema varchar(50),
TableName varchar(100),
ColumnName varchar(100),
DataType varchar(100),
CharMaxLength varchar(100))
EXEC sp_MSForEachDB @command1=’USE [?];
INSERT INTO #yourcolumndetails SELECT
Table_Catalog
,Table_Schema
,Table_Name
,Column_Name
,Data_Type
,Character_Maximum_Length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like ”origin”’
select * from #yourcolumndetails
Drop table #yourcolumndetails